﻿Imports System
Imports System.Collections.Generic
Imports System.Linq
Imports System.Text
Imports BusinessRule.IBusiness
Imports DatabaseAccess.IDAL
Imports System.Data
Imports BusinessRule.Common
Imports DatabaseAccess.DALFactory
Imports External.APICalling
Imports BusinessRule.DTO
Imports System.Configuration
Imports DatabaseAccess.DBUtility

Public Class uMES_JX_ContainerInfoPrintBusiness
    Implements uMESContainerInfoPrintIBusiness

    Private dal As IOracleDAL = uMESDalContext.instance.GetDalObject()

    ''' <summary>
    ''' 获取批次信息
    ''' </summary>
    ''' <returns></returns>
    Private Function GetContainerCurrentInfo(ByVal QueryCondition As Dictionary(Of String, String)) As uMESProcessResult Implements uMESContainerInfoPrintIBusiness.GetContainerCurrentInfo
        Dim oResult As New uMESProcessResult

        Try
            Dim strSQL, strContainername, strFactoryName, strProductName, strEmployeeName As String

            strContainername = ""
            If QueryCondition.Keys.Contains("ContainerName") = True Then
                strContainername = QueryCondition("ContainerName").ToString()
            End If

            strFactoryName = ""
            If QueryCondition.Keys.Contains("FactoryName") = True Then
                strFactoryName = QueryCondition("FactoryName").ToString()
            End If

            strProductName = ""
            If QueryCondition.Keys.Contains("ProductName") = True Then
                strProductName = QueryCondition("ProductName").ToString()
            End If

            strEmployeeName = ""
            If QueryCondition.Keys.Contains("EmployeeName") = True Then
                strEmployeeName = QueryCondition("EmployeeName").ToString()
            End If

            strSQL = "SELECT Mfgorder.mfgordername,NVL(Product.bomid,'') AS bomid, NVL(Product.bombaseid,'') as bombaseid , " & vbCrLf & _
                    "       Container.Containername, DECODE(Inventtaskinfo.Urgent,'0','普通','1','紧急','2','特急') AS Urgent, Container.Qty,Productbase.Productname," & vbCrLf & _
                    "       Product.Description,Productfamily.Productfamilyname,Producttype.Producttypename, " & vbCrLf & _
                    "       Product.Productrevision, Container.Factorystartdate, Container.Containerid, Employee.Fullname" & vbCrLf & _
                    "FROM Container" & vbCrLf & _
                    "LEFT OUTER JOIN Mfgorder ON Container.mfgorderid=Mfgorder.mfgorderid " & vbCrLf & _
                    "LEFT OUTER JOIN Inventtaskinfo ON Mfgorder.Inventtaskinfoid = Inventtaskinfo.Inventtaskinfoid" & vbCrLf & _
                    "INNER JOIN Product on Product.Productid = Container.Productid " & vbCrLf & _
                    "INNER JOIN Productbase on Productbase.Productbaseid =Product.Productbaseid " & vbCrLf & _
                    "LEFT OUTER JOIN Productfamily on Productfamily.Productfamilyid=Product.Productfamilyid " & vbCrLf & _
                    "LEFT OUTER JOIN Producttype on Producttype.producttypeid=Product.producttypeid " & vbCrLf & _
                    "INNER JOIN Currentstatus ON Currentstatus.Currentstatusid = Container.Currentstatusid " & vbCrLf & _
                    "INNER JOIN WorkflowStep ON Currentstatus.WorkflowStepid = WorkflowStep.WorkflowStepid " & vbCrLf & _
                    "INNER JOIN Workflow ON Workflow.Workflowid = WorkflowStep.Workflowid " & vbCrLf & _
                    "INNER JOIN Cdodefinition ON Cdodefinition.Cdodefid = Workflow.Cdotypeid " & vbCrLf & _
                    "LEFT OUTER JOIN Factory ON Currentstatus.Factoryid = Factory.Factoryid" & vbCrLf & _
                    "INNER JOIN Employee ON Employee.Employeeid = Container.startemployeeid" & vbCrLf & _
                    "WHERE Container.Parentcontainerid IS NULL AND Container.Status = 1 AND Container.Qty >= 0 AND Container.Containerid <> Container.Containername" & vbCrLf

            If strContainername <> "" Then
                strSQL = strSQL & "AND Container.Containername LIKE '%" & strContainername & "%'" & vbCrLf
            End If
            If strFactoryName <> "" Then
                strSQL = strSQL & "AND Factory.Factoryname='" & strFactoryName & "'" & vbCrLf
            End If
            If strProductName <> "" Then
                strSQL = strSQL & "AND Productbase.Productname='" & strProductName & "'" & vbCrLf
            End If
            If strEmployeeName <> "" Then
                strSQL = strSQL & "AND UPPER(Employee.Fullname) LIKE UPPER('%" & strEmployeeName & "%')" & vbCrLf
            End If
            strSQL = strSQL & "ORDER BY container.Factorystartdate DESC" & vbCrLf ''Container.Factorystartdate, Container.Containername" & vbCrLf

            Dim paraPage As New uMESPagingDataDTO
            paraPage.CurrentPageIndex = Convert.ToInt32(QueryCondition("CurrentPageIndex").ToString)
            paraPage.PageSize = Convert.ToInt32(QueryCondition("PageSize").ToString)
            paraPage.strSQL = strSQL
            Dim result As New uMESProcessResult

            oResult = dal.RunProcedureForPage(paraPage)
            Dim dt As New DataTable
            dt = oResult.DBTable.Copy
            If dt.Rows.Count > 0 Then
                oResult.DBTable = DataAdjust.FormatDataSetSpecNameByColumName("SpecName", dt)
            End If
            result.Result = True

            Return oResult
        Catch ex As Exception
            oResult.Result = False
            oResult.Message = ex.Message
            Return oResult
        End Try
    End Function

    ''' <summary>
    ''' 获取领料批次信息
    ''' </summary>
    ''' <returns></returns>
    Private Function GetContainerCurrentInfoERP(ByVal QueryCondition As Dictionary(Of String, String)) As uMESProcessResult Implements uMESContainerInfoPrintIBusiness.GetContainerCurrentInfoERP
        Dim oResult As New uMESProcessResult

        Try
            Dim strSQL, strContainername, strFactoryName, strProductName, strEmployeeName As String

            strContainername = ""
            If QueryCondition.Keys.Contains("ContainerName") = True Then
                strContainername = QueryCondition("ContainerName").ToString()
            End If

            strFactoryName = ""
            If QueryCondition.Keys.Contains("FactoryName") = True Then
                strFactoryName = QueryCondition("FactoryName").ToString()
            End If

            strProductName = ""
            If QueryCondition.Keys.Contains("ProductName") = True Then
                strProductName = QueryCondition("ProductName").ToString()
            End If

            strEmployeeName = ""
            If QueryCondition.Keys.Contains("EmployeeName") = True Then
                strEmployeeName = QueryCondition("EmployeeName").ToString()
            End If

            strSQL = "SELECT " & vbCrLf & _
                    "       Container.Containername, Container.Qty,Productbase.Productname," & vbCrLf & _
                    "       Product.Description, " & vbCrLf & _
                    "       Product.Productrevision, Container.Factorystartdate, NVL(Product.bomid,'') AS bomid, NVL(Product.bombaseid,'') as bombaseid ,Container.Containerid, Employee.Fullname," & vbCrLf & _
                    "Cdodefinition.Cdoname,ut.attributevalue, ut1.attributevalue as BeginStep, ut2.attributevalue as CloseContianer FROM Container" & vbCrLf & _
                    "INNER JOIN Product on Product.Productid = Container.Productid " & vbCrLf & _
                    "INNER JOIN Productbase on Productbase.Productbaseid =Product.Productbaseid " & vbCrLf & _
                    "INNER JOIN Currentstatus ON Currentstatus.Currentstatusid = Container.Currentstatusid " & vbCrLf & _
                    "INNER JOIN WorkflowStep ON Currentstatus.WorkflowStepid = WorkflowStep.WorkflowStepid " & vbCrLf & _
                    "INNER JOIN Workflow ON Workflow.Workflowid = WorkflowStep.Workflowid " & vbCrLf & _
                    "INNER JOIN Cdodefinition ON Cdodefinition.Cdodefid = Workflow.Cdotypeid " & vbCrLf & _
                    "LEFT OUTER JOIN Factory ON Currentstatus.Factoryid = Factory.Factoryid" & vbCrLf & _
                    "INNER JOIN Employee ON Employee.Employeeid = Container.startemployeeid" & vbCrLf & _
                    "LEFT JOIN userattribute ut ON ut.parentid=container.containerid AND ut.userattributename='printType'" & vbCrLf & _
                    "LEFT JOIN userattribute ut1 ON ut1.parentid=container.containerid AND ut1.userattributename='BeginStep'" & vbCrLf & _
                    "LEFT JOIN userattribute ut2 ON ut2.parentid=container.containerid AND ut2.userattributename='CloseContianer'" & vbCrLf & _
                " left join movehistory m on m.historyid = Container.containerid and m.pathid is not null" & vbCrLf & _
               " left join workflowstep wfs on wfs.workflowstepid=m.stepid" & vbCrLf & _
                " left join workflow wf on wf.workflowid = wfs.workflowid" & vbCrLf & _
                " left join workflowbase wfb on wfb.workflowbaseid = wf.workflowbaseid" & vbCrLf & _
            "WHERE Container.Parentcontainerid IS NULL AND Container.Status = 1 AND Container.Qty >= 0 AND Container.Containerid <> Container.Containername" & vbCrLf & _
            " and wfb.workflowname='MW' and wf.workflowrevision='1' and wfs.sequence=1" & vbCrLf

            If strContainername <> "" Then
                strSQL = strSQL & "AND Container.Containername LIKE '%" & strContainername & "%'" & vbCrLf
            End If

            'AND  Cdodefinition.Cdoname = 'MachineMfgWorkFlow' 
            If strFactoryName <> "" Then
                strSQL = strSQL & "AND Factory.Factoryname='" & strFactoryName & "'" & vbCrLf
            End If
            If strProductName <> "" Then
                strSQL = strSQL & "AND Productbase.Productname='" & strProductName & "'" & vbCrLf
            End If
            If strEmployeeName <> "" Then
                strSQL = strSQL & "AND UPPER(Employee.Fullname) LIKE UPPER('%" & strEmployeeName & "%')" & vbCrLf
            End If
            strSQL = strSQL & "ORDER BY container.Containername " & vbCrLf ''Container.Factorystartdate, Container.Containername" & vbCrLf

            'Dim dt As DataTable = dal.GetQualityRecordInfoForProductNo(strSQL)
            Dim paraPage As New uMESPagingDataDTO
            paraPage.CurrentPageIndex = Convert.ToInt32(QueryCondition("CurrentPageIndex").ToString)
            paraPage.PageSize = Convert.ToInt32(QueryCondition("PageSize").ToString)
            paraPage.strSQL = strSQL
            Dim result As New uMESProcessResult
            oResult.Result = True
            oResult = dal.RunProcedureForPage(paraPage)

            oResult.DBTable = DataAdjust.FormatDataSetSpecNameByColumName("SpecName", oResult.DBTable)

            For Each row As DataRow In oResult.DBTable.Rows
                If row.IsNull("bombaseid") = False Then
                    If row("bombaseid").ToString().Trim() = "0000000000000000" Then
                        row("bombaseid") = ""
                    End If
                Else
                    row("bombaseid") = ""
                End If

                If row.IsNull("bomid") = False Then
                    If row("bomid").ToString().Trim() = "0000000000000000" Then
                        row("bomid") = ""
                    End If
                Else
                    row("bomid") = ""
                End If
            Next

            Return oResult

        Catch ex As Exception
            oResult.Result = False
            oResult.Message = ex.Message
            Return oResult
        End Try
    End Function

    ''' <summary>
    ''' 获取跟踪卡打印信息
    ''' </summary>
    ''' <returns></returns>
    Private Function GetContainerPrintInfo(ByVal QueryCondition As Dictionary(Of String, String)) As uMESProcessResult Implements uMESContainerInfoPrintIBusiness.GetContainerPrintInfo
        Dim oResult As New uMESProcessResult

        Try
            Dim ds As New DataSet
            Dim strSQL As String = ""
            Dim bomID As String = ""
            Dim bomBaseID As String = ""
            Dim workflowID As String = ""
            Dim isBlank As Boolean = False '打印空白
            Dim conPrintType As String = "1" '1:工序执行检查 2:流水卡打印(入库只打干过的，在制情况只打当前工序之后)
            If QueryCondition.ContainsKey("IsBlank") Then
                If QueryCondition("IsBlank") = "0" Then
                    isBlank = True
                End If
            End If
            If QueryCondition.ContainsKey("ConPrintType") Then
                conPrintType = QueryCondition("ConPrintType")
            End If

            '批次、零件信息
            strSQL = GetSQL_Main(QueryCondition)
            Dim dtMain As DataTable = DatabaseHelper.Query_ReturnDatTable(strSQL)

            '子工艺卡取默认主零件材料牌号，规格，毛料工序 add:Wangjh 20190218
            If dtMain.Rows.Count > 0 Then
                If dtMain.Rows(0)("Productname").ToString.Contains("子工艺") Then
                    Dim faProductDt As DataTable

                    strSQL = String.Format(<string>SELECT  ut3.attributevalue,p.*  FROM product p
                    LEFT JOIN Userattribute ut3 ON ut3.parentid=p.productid AND ut3.userattributename='锻铸件类型'
                    LEFT JOIN productsubproduct pup ON pup.productid=p.productid
                    WHERE pup.subproductid='{0}'</string>.Value, dtMain.Rows(0)("productid").ToString)
                    faProductDt = DatabaseHelper.Query_ReturnDatTable(strSQL)

                    If faProductDt.Rows.Count = 0 Then
                        strSQL = String.Format(<string>SELECT ut3.attributevalue,p.*  FROM containercurrentinfo cci
                        LEFT JOIN productbase pb ON pb.productname=cci.workflowroute
                        LEFT JOIN product p ON p.productid=pb.revofrcdid
                        LEFT JOIN Userattribute ut3 ON ut3.parentid=p.productid AND ut3.userattributename='锻铸件类型'
                        WHERE cci.containerid='{0}'</string>.Value, QueryCondition("Containerid").ToString())
                        faProductDt = DatabaseHelper.Query_ReturnDatTable(strSQL)
                    End If

                    If faProductDt.Rows.Count > 0 Then
                        dtMain.Rows(0)("PlanMaterialtrademark") = faProductDt.Rows(0)("Materialtrademark")
                        dtMain.Rows(0)("PlanMaterialSpecification") = faProductDt.Rows(0)("MaterialSpecification")
                        dtMain.Rows(0)("ProductDesc") = faProductDt.Rows(0)("description")
                        If faProductDt.Rows(0)("attributevalue").ToString = "是" Then
                            dtMain.Rows(0)("materialsize") = "----------"
                        Else
                            dtMain.Rows(0)("materialsize") = faProductDt.Rows(0)("materialsize")
                        End If
                    End If
                    dtMain.AcceptChanges()
                End If
            End If
            '
            dtMain.TableName = "dtMain"
            ds.Tables.Add(dtMain)

            Dim beginStep As String = String.Empty
            If dtMain.Rows.Count > 0 Then
                beginStep = dtMain.Rows(0)("beginstep").ToString()
                bomID = dtMain.Rows(0)("bomid").ToString().Trim()
                '增加一些条件
                QueryCondition("ContainerType") = dtMain.Rows(0)("ContainerType").ToString().Trim()
                QueryCondition("ProductID") = dtMain.Rows(0)("ProductID").ToString().Trim()

                QueryCondition("CurWorkFlowID") = dtMain.Rows(0)("curworkflowid").ToString().Trim() '当前工艺

                If dtMain.Rows(0)("cdoname").ToString().Trim() = "Workflow" And dtMain.Rows(0)("isinstore").ToString() <> "1" Then '非在制
                    QueryCondition("WorkFlowID") = dtMain.Rows(0)("mfgworkflowid").ToString().Trim()
                ElseIf dtMain.Rows(0)("isinstore").ToString() = "1" Then '已入库 
                    QueryCondition("WorkFlowID") = ""
                    If conPrintType = "1" Then
                        QueryCondition("WorkFlowID") = dtMain.Rows(0)("bsworkflowid").ToString().Trim() '入库前的工艺规程
                    End If
                Else
                    QueryCondition("WorkFlowID") = dtMain.Rows(0)("curworkflowid").ToString().Trim()
                End If
                If Not dtMain.Rows(0).IsNull("FAWORKFLOWID") Then '含主工艺则打的是主工艺
                    QueryCondition("WorkFlowID") = dtMain.Rows(0)("FAWORKFLOWID").ToString().Trim()
                End If
                If dtMain.Rows(0).IsNull("splitsequence") Then '如果当前卡为分批出来的，则有分批的工序号
                    QueryCondition("Sequence") = dtMain.Rows(0)("BEGINSEQUENCE").ToString().Trim() '开始工序顺序
                Else
                    QueryCondition("Sequence") = dtMain.Rows(0)("splitsequence").ToString().Trim() '分批工序顺序
                End If
                If String.IsNullOrWhiteSpace(QueryCondition("Sequence")) Then
                    QueryCondition("Sequence") = "1"
                End If

                '如果当前批次在制，在制seq>上述seq，以在制seq为准 add:Wangjh 20230330
                If isBlank = False And conPrintType = "2" And dtMain.Rows(0)("isinstore") = 0 And Val(dtMain.Rows(0)("CurSequence").ToString) > Val(QueryCondition("Sequence")) Then
                    QueryCondition("Sequence") = dtMain.Rows(0)("CurSequence").ToString
                End If
                '
            End If

            '获取批次所有属性
            strSQL = GetSQL_DataPoint(QueryCondition)
            Dim dtDataPoint As DataTable = DatabaseHelper.Query_ReturnDatTable(strSQL)
            If Not dtDataPoint.Rows(0).IsNull("batchcomments") Then
                Dim batchcomments As String = dtDataPoint.Rows(0)("batchcomments").ToString
                If batchcomments.Split(",").Length > 1 Then
                    Dim shiFa As Integer = 0
                    For Each item In batchcomments.Split(",")
                        If item.Split(":").Length > 1 Then
                            shiFa += Val(item.Split(":")(1))
                        End If
                    Next
                    Dim row As DataRow() = dtDataPoint.Select("dataname='实发数/顺序号'")
                    If row.Length > 0 Then
                        dtDataPoint.Select("dataname='实发数/顺序号'")(0)("datavalue") = shiFa.ToString
                        dtDataPoint.Select("dataname='熔炉号母'")(0)("datavalue") = "见炉号表"
                        dtDataPoint.Select("dataname='检验编号'")(0)("datavalue") = "见炉号表"
                    End If
                    dtDataPoint.AcceptChanges()
                End If
            End If
            dtDataPoint.TableName = "dtDataPoint"
            ds.Tables.Add(dtDataPoint)

            '获取工艺路线工序
            Dim dtContainerStep As DataTable
            'If QueryCondition.ContainsKey("SubWorkFlowIDForPrint") Then '工艺审核处，子工艺查看流水卡
            '    dtContainerStep = GetStepInfoByWorkFlowID(QueryCondition("SubWorkFlowIDForPrint"))
            '    dtContainerStep.TableName = "dtContainerStep"
            '    ds.Tables.Add(dtContainerStep)
            'Else
            If dtMain.Rows(0)("Isinstore").ToString().Trim() <> "1" And QueryCondition.ContainsKey("PdmSubWf") Then '非入库的批次
                strSQL = GetSql_ContainerStepForSub(QueryCondition, New DataTable) '子工艺查看
            Else
                strSQL = GetWorkflowStepForPrint(QueryCondition)
            End If
            dtContainerStep = DatabaseHelper.Query_ReturnDatTable(strSQL)

            If isBlank = False Then
                ContainerStepForPrint(QueryCondition, dtContainerStep, ds) '待打印的工序处理,增加加工记录
            Else
                dtContainerStep.TableName = "dtContainerStep"
                ds.Tables.Add(dtContainerStep)
            End If

            'add zhunan 2022.1.18 添加【是否外委】信息
            dtContainerStep = ds.Tables("dtContainerStep")

            If Not dtContainerStep.Columns.Contains("ISCOMMISSION") Then
                dtContainerStep.Columns.Add("ISCOMMISSION")
            End If
            Dim dtContainerCommission As DataTable = GetContainerCommissioning(QueryCondition("Containerid").ToString()) '批次外委表

            For Each dr As DataRow In dtContainerStep.Rows
                Dim commRows() As DataRow = dtContainerCommission.Select(String.Format("workflowstepid <= '{0}' and inworkflowstepid > '{1}' and workflowid = '{2}'", dr("workflowstepid").ToString, dr("workflowstepid").ToString, dr("workflowid").ToString))

                '判断是否外委
                If commRows.Length > 0 Then
                    dr("ISCOMMISSION") = "1"
                Else
                    dr("ISCOMMISSION") = "0"
                End If
            Next

            Dim dtWorkflow = dtContainerStep.DefaultView.ToTable(True, "workflowid")
            '检测项历史版本的也要查 add:Wangjh 20220616
            Dim workflowIds As String = ""
            For Each row In dtWorkflow.Rows
                workflowIds += "'" + row("workflowid") + "',"
            Next
            workflowIds = workflowIds.Trim(",")
            If String.IsNullOrWhiteSpace(workflowIds) Then
                workflowIds = "''"
            End If
            QueryCondition.Add("WorkFlowIDs", workflowIds)
            '

            '检测项
            Dim dtCheckItemList As DataTable = New DataTable
            If dtMain.Rows(0)("ContainerType").ToString = "7" Then
                strSQL = GetSQL_StepCheckItemList2(QueryCondition) '新数据表
                dtCheckItemList = DatabaseHelper.Query_ReturnDatTable(strSQL)
            End If
            'If dtCheckItemList.Rows.Count = 0 Then
            '    strSQL = GetSQL_CheckItemList(QueryCondition) '旧数据表
            '    dtCheckItemList = dal.GetQualityRecordInfoForProductNo(strSQL)
            'End If
            dtCheckItemList.TableName = "dtCheckItemList"
            ds.Tables.Add(dtCheckItemList)
            Dim strproduct = ""
            If dtMain.Rows.Count > 0 Then
                strproduct = dtMain.Rows(0)("Productname")
            End If

            'Bom
            'strSQL = GetSQL_BOM(bomID, QueryCondition("WorkFlowID"), dtMain.Rows(0)("ProductID").ToString)
            Dim para As Dictionary(Of String, String) = New Dictionary(Of String, String)()
            para("bomid") = bomID
            para("workflowid") = QueryCondition("WorkFlowID").ToString()
            para("productid") = dtMain.Rows(0)("ProductID").ToString
            para("containerid") = QueryCondition("Containerid").ToString()
            strSQL = GetSQL_BOM(para)

            Dim dt As DataTable = dal.GetQualityRecordInfoForProductNo(strSQL)
            Dim dtBom As DataTable = dt.Clone
            For Each row As DataRow In dt.Rows
                If row.IsNull("referencedesignator") Or row("referencedesignator").ToString <> "中间件" Then
                    dtBom.ImportRow(row)
                End If

                If row("referencedesignator").ToString = "中间件" Then '中间件特殊处理
                    Dim qtyrequired As Integer = Val(row("qtyrequired").ToString)
                    Dim zjSql As String = "SELECT t.idnrk,t.maktx2,t.kmpmg FROM MBOMTABLE_TMP T WHERE t.matnr='" + row("ProductName").ToString + "'"
                    Dim zjDt As DataTable = DatabaseHelper.Query_ReturnDatTable(zjSql)
                    For Each zjRow As DataRow In zjDt.Rows
                        row("ProductName") = zjRow("idnrk")
                        row("description") = zjRow("maktx2")
                        row("qtyrequired") = Val(zjRow("kmpmg").ToString) * qtyrequired
                        dtBom.ImportRow(row)
                    Next
                ElseIf row("MATERIALTXNLOGIC").ToString = "1" Then 'BOM中零件类型是批次（序列号）
                    For i As Integer = 1 To Val(row("qtyrequired").ToString) - 1
                        dtBom.ImportRow(row)
                    Next
                End If
            Next
            dtBom.TableName = "dtBom"
            ds.Tables.Add(dtBom)

            '获取集件信息 add:Wangjh 20180109
            Dim issueDt As New DataTable
            Dim issuePara As New Dictionary(Of String, String)
            issuePara.Add("ContainerID", QueryCondition("Containerid"))
            issuePara.Add("BomID", bomID)
            issuePara.Add("WorkFlowID", QueryCondition("WorkFlowID"))
            issueDt = GetContainerIssueInfo(issuePara) 'modify:wangjh 20230302 GetCollectPieceManageBOMList(issuePara)--->GetContainerIssueInfo

            If isBlank = True Then
                issueDt.Rows.Clear()
            End If
            issueDt.TableName = "dtIssue"
            ds.Tables.Add(issueDt)
            '
            'create:Wangjh 2017-7-27 增加分批信息的查询
            Dim splitSql As String = GetSQL_Split(QueryCondition("Containerid").ToString())
            Dim dtSplitInfo As DataTable = DatabaseHelper.Query_ReturnDatTable(splitSql)
            dtSplitInfo.TableName = "dtSplitInfo"
            ds.Tables.Add(dtSplitInfo)

            'add zhunan 增加热表批次号信息
            Dim getRbContainerListSql As String = <string>
                    SELECT DISTINCT c2.rbtogetherflag
                    FROM containersubcontainer csub
                    LEFT JOIN container c2 ON c2.containerid = csub.subcontainerid AND c2.status = 1
                    WHERE csub.containerid = '{0}' AND c2.rbcontainertype IS NOT NULL
                                                  </string>

            getRbContainerListSql = String.Format(getRbContainerListSql, QueryCondition("Containerid"))
            Dim dtRbContainerList As DataTable = DatabaseHelper.Query_ReturnDatTable(getRbContainerListSql.ToString())
            dtRbContainerList.TableName = "dtRbContainerList"
            ds.Tables.Add(dtRbContainerList)

            'add zhunan 2022.10.27 增加查询各子工艺工序其各自的热表批次号
            Dim getSubWorkflowstepListSql As String = <string>
                    SELECT DISTINCT c2.rbtogetherflag,csub.workflowstepid,csub.workflowstepname
                    FROM containersubcontainer csub
                    LEFT JOIN container c2 ON c2.containerid = csub.subcontainerid AND c2.status = 1
                    WHERE csub.containerid = '{0}' AND c2.rbcontainertype IS NOT NULL
                                                  </string>

            getSubWorkflowstepListSql = String.Format(getSubWorkflowstepListSql, QueryCondition("Containerid"))
            Dim dtSubWorkflowstepList As DataTable = DatabaseHelper.Query_ReturnDatTable(getSubWorkflowstepListSql.ToString())
            dtSubWorkflowstepList.TableName = "dtSubWorkflowstepList"
            ds.Tables.Add(dtSubWorkflowstepList)

            oResult.DBDataSet = ds
            oResult.Result = True
            Return oResult
        Catch ex As Exception
            oResult.Result = False
            oResult.Message = ex.Message
            Return oResult
        End Try
    End Function
    ''' <summary>
    ''' 获取批次外委数据 add zhangrj 2022-06-09
    ''' </summary>
    ''' <param name="contianerId"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function GetContainerCommissioning(ByVal contianerId As String) As DataTable
        Dim sql As String = <string>
 SELECT cc.workflowstepid,cc.inworkflowstepid,ws.workflowid FROM containercommissioning cc 
LEFT JOIN workflowstep ws ON ws.workflowstepid = cc.workflowstepid
WHERE cc.containerid = '{0}' and cc.isuse = 0                               
                            </string>
        sql = String.Format(sql, contianerId)
        Return DatabaseHelper.Query_ReturnDatTable(sql.ToString)
    End Function

    ''' <summary>
    ''' 工艺信息预览
    ''' add:Wangjh 20180829
    ''' </summary>
    ''' <param name="QueryCondition"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function GetWorkflowPrintView(ByVal QueryCondition As Dictionary(Of String, String)) As uMESProcessResult Implements uMESContainerInfoPrintIBusiness.GetWorkflowPrintView
        Dim oResult As New uMESProcessResult
        Dim ds As New DataSet
        Dim strSQL As String = ""
        Dim bomID As String = QueryCondition("BomID")
        Dim bomBaseID As String = ""
        Dim workflowID As String = ""

        Dim dtMain As New DataTable

        dtMain.Columns.Add("splitqty")
        dtMain.Columns.Add("qty")
        dtMain.Columns.Add("mfgordername")
        dtMain.Columns.Add("containername")
        dtMain.Columns.Add("familylistnames")
        dtMain.Columns.Add("originalstartdate")
        dtMain.Columns.Add("productname")
        dtMain.Columns.Add("bomid")
        dtMain.Columns.Add("productid")
        dtMain.Columns.Add("ProductDesc")
        dtMain.Columns.Add("originalqty")
        dtMain.Columns("originalqty").DefaultValue = 0
        dtMain.Columns.Add("uomname")
        dtMain.Columns.Add("PlanFullName")

        dtMain.Columns.Add("BeginStep")
        dtMain.Columns.Add("CloseContianer")
        dtMain.Columns.Add("组别件号")
        dtMain.Columns.Add("PlanMaterialtrademark")
        dtMain.Columns.Add("PlanMaterialSpecification")
        dtMain.Columns.Add("isselfmfg")
        dtMain.Columns.Add("curworkflowid")
        dtMain.Columns.Add("cdoname")
        dtMain.Columns.Add("mfgworkflowid")
        dtMain.Columns.Add("BEGINSEQUENCE")
        dtMain.Columns.Add("splitsequence")
        dtMain.Columns.Add("CONTAINERTYPE")
        dtMain.Columns.Add("isinstore")

        dtMain.Columns.Add("charactertype")
        dtMain.Columns.Add("TECHNIQUEEMPLOYNAME")
        dtMain.Columns.Add("workflowtype")
        dtMain.Columns.Add("process_no")
        dtMain.Columns.Add("Part_RevID")
        dtMain.Columns.Add("ly2sequence")
        dtMain.Columns.Add("wffactoryname")
        dtMain.Columns.Add("wffactoryid")
        dtMain.Columns.Add("FAWORKFLOWID")
        dtMain.Columns.Add("workflowroute")
        dtMain.Columns.Add("materialSize")
        dtMain.Rows.Add()
        dtMain.TableName = "dtMain"
        ds.Tables.Add(dtMain)

        '
        Dim dtDataPoint As New DataTable
        dtDataPoint.Columns.Add("splitcontainername")
        dtDataPoint.Columns.Add("checkemployeeName")
        dtDataPoint.Columns.Add("splitfromid")
        dtDataPoint.Columns.Add("qty")
        dtDataPoint.Columns.Add("datavalue")
        dtDataPoint.Columns.Add("dataname")
        dtDataPoint.Rows.Add()
        dtDataPoint.TableName = "dtDataPoint"
        ds.Tables.Add(dtDataPoint)
        '

        '工序查询
        QueryCondition("Sequence") = 0
        strSQL = GetWorkflowStepForPrint(QueryCondition)
        Dim dtContainerStep = DatabaseHelper.Query_ReturnDatTable(strSQL)
        dtContainerStep.Columns.Add("NOTES")
        dtContainerStep.TableName = "dtContainerStep"
        ds.Tables.Add(dtContainerStep)
        '
        '检测项
        strSQL = GetSQL_StepCheckItemList(QueryCondition) '新数据表
        Dim dtCheckItemList = DatabaseHelper.Query_ReturnDatTable(strSQL)
        dtCheckItemList.TableName = "dtCheckItemList"
        ds.Tables.Add(dtCheckItemList)
        '

        'Bom
        strSQL = GetSQL_BOM(bomID, QueryCondition("WorkFlowID"), "")
        Dim dt As DataTable = DatabaseHelper.Query_ReturnDatTable(strSQL)
        Dim dtBom As DataTable = dt.Clone
        For Each row As DataRow In dt.Rows
            If row.IsNull("referencedesignator") Or row("referencedesignator").ToString <> "中间件" Then
                dtBom.ImportRow(row)
            End If

            If row("referencedesignator").ToString = "中间件" Then '中间件特殊处理
                Dim qtyrequired As Integer = Val(row("qtyrequired").ToString)
                Dim zjSql As String = "SELECT t.idnrk,t.maktx2,t.kmpmg FROM MBOMTABLE_TMP T WHERE t.matnr='" + row("ProductName").ToString + "'"
                Dim zjDt As DataTable = DatabaseHelper.Query_ReturnDatTable(zjSql)
                For Each zjRow As DataRow In zjDt.Rows
                    row("ProductName") = zjRow("idnrk")
                    row("description") = zjRow("maktx2")
                    row("qtyrequired") = Val(zjRow("kmpmg").ToString) * qtyrequired
                    dtBom.ImportRow(row)
                Next
            ElseIf row("MATERIALTXNLOGIC").ToString = "1" Then 'BOM中零件类型是批次（序列号）
                For i As Integer = 1 To Val(row("qtyrequired").ToString) - 1
                    dtBom.ImportRow(row)
                Next
            End If
        Next
        dtBom.TableName = "dtBom"
        ds.Tables.Add(dtBom)
        '

        oResult.DBDataSet = ds
        oResult.Result = True
        Return oResult

    End Function

    ''' <summary>
    ''' 获取跟踪卡BOM信息
    ''' </summary>
    ''' <param name="QueryCondition"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function GetContainerBomPrintInfo(ByVal QueryCondition As Dictionary(Of String, String)) As uMESProcessResult Implements uMESContainerInfoPrintIBusiness.GetContainerBomPrintInfo
        Dim result As New uMESProcessResult
        Dim strSql As String = ""
        Dim bomID As String = ""
        Dim ds As New DataSet
        '批次、零件信息
        strSql = GetSQL_Main(QueryCondition)
        Dim dtMain As DataTable = DatabaseHelper.Query_ReturnDatTable(strSql)
        dtMain.TableName = "dtMain"
        ds.Tables.Add(dtMain)
        If dtMain.Rows.Count > 0 Then
            bomID = dtMain.Rows(0)("bomid").ToString().Trim()
        End If
        If dtMain.Rows(0)("cdoname").ToString().Trim() = "Workflow" Then '非在制
            QueryCondition("WorkFlowID") = dtMain.Rows(0)("mfgworkflowid").ToString().Trim()
        Else
            QueryCondition("WorkFlowID") = dtMain.Rows(0)("curworkflowid").ToString().Trim()
        End If
        'Bom
        strSql = GetSQL_BOM(bomID, QueryCondition("WorkFlowID"), dtMain.Rows(0)("ProductID").ToString)
        Dim dt As DataTable = dal.GetQualityRecordInfoForProductNo(strSql)
        Dim dtBom As DataTable = dt.Clone
        For Each row As DataRow In dt.Rows
            dtBom.ImportRow(row)
            If row("MATERIALTXNLOGIC").ToString = "1" Then 'BOM中零件类型是批次（序列号）
                For i As Integer = 1 To Val(row("qtyrequired").ToString) - 1
                    dtBom.ImportRow(row)
                Next
            End If
        Next
        dtBom.TableName = "dtBom"
        ds.Tables.Add(dtBom)
        result.DBDataSet = ds
        result.Result = True
        Return result
    End Function

    ''' <summary>
    ''' 获取批次所有子工艺的打印信息
    ''' add:Wangjh 20180321
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetContainerPrintInfoForSub(ByVal para As Dictionary(Of String, String)) As uMESProcessResult Implements uMESContainerInfoPrintIBusiness.GetContainerPrintInfoForSub
        Dim oResult As New uMESProcessResult
        Dim ds As New DataSet
        Dim strSQL As String = ""
        Dim bomID As String = ""
        Dim bomBaseID As String = ""
        Dim workflowID As String = ""
        '批次、零件信息
        strSQL = GetSQL_Main(para)
        Dim dtMain As DataTable = DatabaseHelper.Query_ReturnDatTable(strSQL)
        dtMain.TableName = "dtMain"
        ds.Tables.Add(dtMain)

        para.Add("ProductID", dtMain.Rows(0)("ProductID"))

        Dim beginStep As String = String.Empty
        If dtMain.Rows.Count > 0 Then
            beginStep = dtMain.Rows(0)("beginstep").ToString()
            bomID = dtMain.Rows(0)("bomid").ToString().Trim()
            If Not dtMain.Rows(0).IsNull("splitsequence") Then '如果当前卡为分批出来的，则有分批的工序号
                para("Sequence") = dtMain.Rows(0)("splitsequence").ToString().Trim() '分批工序顺序
            End If
        End If

        '数据采集点
        strSQL = GetSQL_DataPoint(para)
        Dim dtDataPoint As DataTable = DatabaseHelper.Query_ReturnDatTable(strSQL)
        dtDataPoint.TableName = "dtDataPoint"
        ds.Tables.Add(dtDataPoint)

        '查询批次子工艺工序信息语句
        Dim dt1 As DataTable = New DataTable
        strSQL = GetSql_ContainerStepForSub(para, dt1) '批次当前工艺工序信息
        Dim dtContainerStep As DataTable = DatabaseHelper.Query_ReturnDatTable(strSQL)
        dtContainerStep.Columns.Add("ReportQty")
        dtContainerStep.Columns.Add("ReportEmployeeName")
        dtContainerStep.Columns.Add("passqty")
        dtContainerStep.Columns.Add("lossqty")
        dtContainerStep.Columns.Add("defectqty")
        dtContainerStep.Columns.Add("checkemployeename")
        dtContainerStep.Columns.Add("reportdate")
        dtContainerStep.TableName = "dtContainerStep"
        'ds.Tables.Add(dtContainerStep)

        Dim temView2 As DataView = dtContainerStep.Copy.DefaultView
        Dim subWorkFlowDt As DataTable = temView2.ToTable(True, "WorkFlowID")
        If Not para.ContainsKey("IsBlank") Then
            For Each dr In subWorkFlowDt.Rows
                para("CurWorkFlowID") = dr("workflowid").ToString
                ContainerStepForPrint(para, dtContainerStep, ds)
            Next
            dtContainerStep.DefaultView.Sort = " specno asc"
            dtContainerStep = dtContainerStep.DefaultView.ToTable
            ds.Tables("dtContainerStep").Clear()
            ds.Tables("dtContainerStep").Merge(dtContainerStep)
        Else
            ds.Tables.Add(dtContainerStep)
        End If

        '检测项
        Dim temView As DataView = dtContainerStep.DefaultView
        Dim workflowDrs As DataTable = temView.ToTable(True, "WorkFlowID", "BOMID")


        Dim dtCheckItemList As DataTable = New DataTable
        For i As Integer = 0 To workflowDrs.Rows.Count - 1
            para("WorkFlowID") = workflowDrs.Rows(i)("WorkFlowID").ToString
            strSQL = GetSQL_StepCheckItemList(para) '新数据表
            If i = 0 Then
                dtCheckItemList = DatabaseHelper.Query_ReturnDatTable(strSQL)
            Else
                dtCheckItemList.Merge(DatabaseHelper.Query_ReturnDatTable(strSQL))
            End If
        Next
        dtCheckItemList.TableName = "dtCheckItemList"
        ds.Tables.Add(dtCheckItemList)

        'Bom
        Dim dtBom As DataTable = New DataTable
        Dim temDt As DataTable = New DataTable
        For Each row As DataRow In workflowDrs.Rows
            workflowID = String.Format("{0}{1},", workflowID, row("workflowid").ToString)
            bomID = String.Format("{0}{1},", bomID, row("bomid").ToString)
            strSQL = GetSQL_BOM(row("bomid").ToString, row("workflowid").ToString, dtMain.Rows(0)("ProductID").ToString)
            temDt = DatabaseHelper.Query_ReturnDatTable(strSQL)
            If dtBom.Columns.Count = 0 Then
                dtBom = temDt.Clone
            End If
            For Each _row As DataRow In temDt.Rows
                dtBom.ImportRow(_row)
                If _row("MATERIALTXNLOGIC").ToString = "1" Then 'BOM中零件类型是批次（序列号）
                    For i As Integer = 1 To Val(_row("qtyrequired").ToString) - 1
                        dtBom.ImportRow(_row)
                    Next
                End If
            Next
        Next

        dtBom.TableName = "dtBom"
        ds.Tables.Add(dtBom)

        '获取集件信息 add:Wangjh 20180109
        Dim issueDt As New DataTable
        If Not para.ContainsKey("IsBlank") Then
            Dim issuePara As New Dictionary(Of String, String)
            issuePara.Add("ContainerID", para("Containerid"))
            issuePara.Add("BomID", bomID)
            issuePara.Add("WorkFlowID", workflowID)
            issueDt = GetCollectPieceManageBOMList(issuePara)
        End If
        issueDt.TableName = "dtIssue"
        ds.Tables.Add(issueDt)
        '

        'strSQL = <STRING>
        '             SELECT E.FULLNAME,sub.qty FROM SUBWORKFLOWCHECKINFO SUB 
        '            LEFT JOIN WORKFLOW W ON W.WORKFLOWID=SUB.SUBWORKFLOWID
        '            LEFT JOIN EMPLOYEE E ON E.EMPLOYEEID=SUB.CHECKTECHNIQUEID
        '         </STRING>
        'strSQL += String.Format(" WHERE W.PROCESS_NO='{0}' AND SUB.CONTAINERID='{1}'", para("PdmSubWf"), para("Containerid"))
        'Dim dt1 As DataTable = New DataTable
        'dt1 = DatabaseHelper.Query_ReturnDatTable(strSQL)
        dt1.TableName = "dt1"
        ds.Tables.Add(dt1)

        '打子工艺时数量为SUBWORKFLOWCHECKINFO里的数量
        If Not dt1.Rows(0).IsNull("qty") Then
            ds.Tables("dtMain").Rows(0)("Originalqty") = dt1.Rows(0)("qty")
        End If
        '

        Dim dtTemp1 As DataTable = New DataTable
        dtTemp1.TableName = "dt2"
        ds.Tables.Add(dtTemp1)
        Dim dtTemp2 As DataTable = New DataTable
        dtTemp2.TableName = "dt3"
        ds.Tables.Add(dtTemp2)
        Dim dt4Tmp As DataTable = GetConventCheckInfo(para) '需要更改总表 Wangjh 2017-6-29 已改 
        ds.Tables.Add(dt4Tmp)
        'create:Wangjh 2017-7-27 增加分批信息的查询
        Dim splitSql As String = GetSQL_Split(para("Containerid"))
        Dim dtSplitInfo As DataTable = DatabaseHelper.Query_ReturnDatTable(splitSql)
        dtSplitInfo.TableName = "dtSplitInfo"
        ds.Tables.Add(dtSplitInfo)

        oResult.DBDataSet = ds
        oResult.Result = True
        Return oResult
    End Function

    ''' <summary>
    ''' 查询批次子工艺工序信息语句
    ''' </summary>
    ''' <param name="QueryCondition"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function GetSql_ContainerStepForSub(ByVal QueryCondition As Dictionary(Of String, String), ByRef subDt As DataTable)
        Dim strSql As String = <string>
                                  SELECT E.FULLNAME,SWC.* FROM SUBWORKFLOWCHECKINFO SWC
                                LEFT JOIN WORKFLOW W ON W.WORKFLOWID=SWC.SUBWORKFLOWID
                                LEFT JOIN EMPLOYEE E ON E.EMPLOYEEID=SWC.CHECKTECHNIQUEID
                              </string>
        strSql += String.Format(" WHERE SWC.CONTAINERID='{0}' AND W.PROCESS_NO='{1}'", QueryCondition("Containerid"), QueryCondition("PdmSubWf"))
        strSql += " ORDER BY SWC.CREATEDATE DESC"
        subDt = DatabaseHelper.Query_ReturnDatTable(strSql)

        If subDt.Rows.Count = 0 Then
            Return ""
        End If

        strSql = <string>
                    SELECT c.containerid,pb.productname,subw.workflowid,sube2.workflowname,ws.workflowstepname,sb1.specname,s.specid,s.iskey,ws2.sequence,ws2.workflowstepid,s.opdesc,nvl(s.specno,substr(ws2.workflowstepname,0,INSTR(ws2.workflowstepname,'-')-1)) specno,ws.controlcode,ws.steptype,b.bomid FROM workflow w 
	                LEFT JOIN workflowstep ws ON ws.workflowid=w.workflowid AND ws.steptype=2
	                LEFT JOIN workflowbase sube ON sube.workflowbaseid=ws.subworkflowbaseid
	                LEFT JOIN workflow subw ON subw.workflowid=NVL(sube.revofrcdid,ws.subworkflowid)
	                LEFT JOIN workflowbase sube2 ON sube2.workflowbaseid=subw.workflowbaseid 
                    LEFT JOIN subworkflowcheckinfo sub ON sub.workflowid=w.workflowid AND sub.subworkflowid=subw.workflowid AND ROWNUM=1
	                LEFT JOIN container c ON c.containerid=sub.containerid
                    LEFT JOIN product p ON p.productid=c.productid
	                LEFT JOIN productbase pb ON pb.productbaseid=p.productbaseid
	                LEFT JOIN workflowstep ws2 ON ws2.workflowid=subw.workflowid
	                LEFT JOIN specbase sb ON sb.specbaseid=ws2.specbaseid
	                LEFT JOIN spec s ON s.specid=NVL(sb.revofrcdid,ws2.specid)
	                LEFT JOIN specbase sb1 ON sb1.specbaseid=s.specbaseid
                    LEFT JOIN bom b ON b.workflowid=subw.workflowid
                </string>
        strSql += String.Format(" WHERE c.containerid='{0}' AND subw.process_no='{1}' and sub.workflowid='{2}' AND (WS2.CONTROLCODE<>'LY07' OR WS2.CONTROLCODE IS NULL)", QueryCondition("Containerid"), QueryCondition("PdmSubWf"), subDt.Rows(0)("workflowid").ToString)
        ' strSql += String.Format(" WHERE sub.subworkflowcheckinfoid='{0}'", subDt.Rows(0)("subworkflowcheckinfoid"))
        If QueryCondition.ContainsKey("Sequence") Then
            strSql += String.Format(" AND WS2.Sequence>={0}", QueryCondition("Sequence"))
        End If
        strSql += "  GROUP BY c.containerid,pb.productname,subw.workflowid,sube2.workflowname,ws.workflowstepname,sb1.specname,s.specid,s.iskey,ws2.sequence,ws2.workflowstepid,s.opdesc,s.specno,ws.controlcode,ws.steptype,b.bomid,ws2.workflowstepname"
        strSql += " ORDER BY subw.workflowid,s.specno ASC "
        GetSql_ContainerStepForSub = strSql
    End Function

    Private Function GetSQL_Main(ByVal QueryCondition As Dictionary(Of String, String)) As String
        Dim strSQL = "", strContainerid As String

        strContainerid = ""
        If QueryCondition.Keys.Contains("Containerid") = True Then
            strContainerid = QueryCondition("Containerid").ToString()
        End If
        '查询分批一些信息
        '当前卡为原卡 
        Dim splitqty As String = ""
        Dim splitSql As String = "SELECT * FROM containersplitinfo csi WHERE csi.splitresult=0 AND csi.isuse=0 AND csi.containerid='" + strContainerid + "' ORDER BY csi.createdate DESC"
        Dim splitDt As DataTable = DatabaseHelper.Query_ReturnDatTable(splitSql)
        If splitDt.Rows.Count > 0 Then
            splitqty = splitDt.Rows(0)("qty").ToString
        End If
        '当前卡为分出来的卡
        Dim splitCheckDate As String = "null"
        Dim splitsequence As String = ""
        splitSql = <string>SELECT csi.checkdata,ws.sequence SPLITsequence FROM containersplitinfo csi
										LEFT JOIN container c ON c.splitfromid=csi.containerid AND csi.splitcontainername=c.containername
										LEFT JOIN Workflowstep ws ON ws.workflowstepid=csi.workflowstepid
										WHERE csi.splitresult=0 AND csi.isuse=0 </string>.Value + "AND c.containerid='" + strContainerid + "'" +
          " ORDER BY csi.createdate DESC"
        splitDt = DatabaseHelper.Query_ReturnDatTable(splitSql)
        If splitDt.Rows.Count > 0 Then
            splitCheckDate = "'" + splitDt.Rows(0)("checkdata").ToString + "'"
            splitsequence = splitDt.Rows(0)("SPLITsequence").ToString
        End If
        'yuxj20221028增加临时工艺workflowtype2
        'zhunan2023.01.17增加查询批次后缀 customobjectitemsname
        strSQL = String.Format(<string>SELECT '{0}' splitqty,cci.qty,M.mfgordername,m.lgort,cci.containername,cci.familylistnames,NVL({1},c.originalstartdate) originalstartdate,cci.productname, 
                    cci.bomid,cci.productid,cci.productdescription ProductDesc,c.originalqty,u.uomname,PlanEmployee.Fullname AS PlanFullName, 
                    ut1.attributevalue as BeginStep, ut2.attributevalue as CloseContianer,p.Materialtrademark AS PlanMaterialtrademark,p.MaterialSpecification PlanMaterialSpecification,c.isselfmfg,
                    W.WORKFLOWID curworkflowid,cdo.cdoname,c.mfgworkflowid,FWS.SEQUENCE BEGINSEQUENCE,'{2}' splitsequence,CCI.CONTAINERTYPE,cci.isinstore,p.charactertype,e2.fullname TECHNIQUEEMPLOYNAME,
                    DECODE(cci.isinstore,-1,w2.workflowtype,0,w.workflowtype,1,w3.workflowtype) workflowtype, DECODE(cci.isinstore,-1,w2.process_no,0,w.process_no,1,w3.process_no) process_no,DECODE(cci.isinstore,-1,w2.Part_RevID,0,w.Part_RevID,1,w3.Part_RevID) Part_RevID,
                    DECODE(cci.isinstore,-1,w2.ly2sequence,0,w.ly2sequence,1,w3.ly2sequence) ly2sequence,cci.bsworkflowid,--入库前工艺 
                    fa.factoryname wffactoryname,fa.factoryid wffactoryid,null FAWORKFLOWID,cci.workflowroute,DECODE(cci.isinstore,-1,w2.workflowtype2,0,w.workflowtype2,1,w3.workflowtype2) workflowtype2,
                    DECODE(ut3.attributevalue,'是','----------',p.materialsize) materialsize,obj.customobjectitemsname ,cci.Workflowstepsequence cursequence,m.zhbmodel
                    FROM CONTAINERCURRENTINFO CCI 
                    LEFT JOIN container c ON c.containerid=cci.containerid 
                    LEFT JOIN MFGORDER M ON M.MFGORDERID=CCI.MFGORDERID AND M.CDOTYPEID=1620
                    LEFT JOIN ORDERTYPE ot ON ot.ordertypeid=m.ordertypeid
                    LEFT JOIN product p ON p.productid=cci.productid
                    LEFT JOIN WORKFLOW W ON W.WORKFLOWID=CCI.WORKFLOWID --当前工艺
					LEFT JOIN workflow w2 ON w2.workflowid=c.mfgworkflowid--开卡工艺
                    LEFT JOIN WORKFLOW W3 ON W3.WORKFLOWID=CCI.bsWORKFLOWID --入库前工艺
                    LEFT JOIN cdodefinition cdo ON cdo.cdodefid=w.cdotypeid 
                    LEFT JOIN uom u ON u.uomid=c.uomid 
                    LEFT JOIN Employee PlanEmployee ON PlanEmployee.Employeeid = c.startemployeeid 
                    LEFT JOIN userattribute ut1 ON ut1.parentid=c.containerid AND ut1.userattributename='BeginStep' 
                    LEFT JOIN WORKFLOWSTEP FWS ON FWS.WORKFLOWSTEPNAME=ut1.attributevalue AND FWS.WORKFLOWID=c.mfgworkflowid 
                    LEFT JOIN userattribute ut2 ON ut2.parentid=c.containerid AND ut2.userattributename='CloseContianer' 
                    LEFT JOIN Userattribute ut3 ON ut3.parentid=p.productid AND ut3.userattributename='锻铸件类型'
                    LEFT JOIN Employee e2 ON e2.Employeeid =CCI.TECHNIQUEEMPLOYEEID
                    LEFT JOIN Factory fa ON fa.factoryid=DECODE(cci.isinstore,-1,w2.factoryid,0,w.factoryid,1,w3.factoryid)
                    LEFT JOIN customobjectitems obj ON obj.customobjectitemsid = cci.containersuffixid
                 </string>.Value, splitqty, splitCheckDate, splitsequence)
        strSQL += String.Format("WHERE c.ContainerID='{0}'", strContainerid)
        Return strSQL
    End Function

    Private Function GetSQL_DataPoint(ByVal QueryCondition As Dictionary(Of String, String)) As String
        Dim strSQL = "", strContainerid As String
        strContainerid = ""
        If QueryCondition.Keys.Contains("Containerid") = True Then
            strContainerid = QueryCondition("Containerid").ToString()
        End If

        'strSQL = "select c.containername, h.containerid,h.workflowstepid,t.dataname,t.datavalue,h.txndate from datapointhistorydetail t " & vbCrLf & _
        '         "left join datapointhistory dt on dt.datapointhistoryid=t.datapointhistoryid " & vbCrLf & _
        '         "left join historymainline h on h.historymainlineid=dt.historymainlineid " & vbCrLf & _
        '         "left join container c on c.containerid=h.containerid " & vbCrLf & _
        '         "where c.containerid='" & strContainerid & "' order by h.txndate desc"

        'update:Wangjh 2017-7-25 增加分批信息的查询
        strSQL = "select c.batchcomments,csi.splitcontainername,e.fullname checkemployeeName,c.splitfromid,c.originalqty,us.attributevalue datavalue,us.userattributename dataname from userattribute us " & vbCrLf & _
                " LEFT JOIN container c ON c.containerid=us.parentid" & vbCrLf & _
                " LEFT JOIN containersplitinfo csi ON csi.splitcontainername=c.containername and csi.isuse=0 and csi.splitresult=0 " & vbCrLf & _
                "LEFT JOIN employee e ON e.employeeid=csi.checkemployeeid" & vbCrLf & _
                " where us.parentid='" & strContainerid & "' order by datavalue"

        Return strSQL
    End Function

    Private Function GetSQL_ContainerStep(ByVal QueryCondition As Dictionary(Of String, String)) As String '批次当前工序信息
        Dim strSQL = "", strContainerid As String
        strContainerid = ""
        If QueryCondition.Keys.Contains("Containerid") = True Then
            strContainerid = QueryCondition("Containerid").ToString()
        End If
        Dim sb = New StringBuilder()
        sb.Append("select c.containerid,pb.productname,w.workflowid ,wb.workflowname,ws.workflowstepname,sb1.specname,")
        sb.Append("s.specid,s.iskey, ws.sequence,cc.cdoname,WS.SUBWORKFLOWID,WS.SUBWORKFLOWBASEID,s.opdesc,s.specno,WS.CONTROLCODE,ws.steptype,NULL BOMID from container c ") 'add:wangjh 增加opdesc和specno查询
        sb.Append("LEFT JOIN product p ON p.productid=c.productid ")
        sb.Append("LEFT JOIN productbase pb ON pb.productbaseid=p.productbaseid ")
        sb.Append("LEFT JOIN currentstatus cu ON cu.currentstatusid=c.currentstatusid ")
        sb.Append("LEFT JOIN Workflowstep wp ON wp.workflowstepid=cu.workflowstepid ")
        sb.Append("left join workflow w on w.workflowid = Wp.workflowid ")
        sb.AppendLine("left join workflowstep ws on ws.workflowid=w.workflowid")
        sb.Append("left join workflowbase wb on wb.workflowbaseid = w.workflowbaseid ")
        sb.Append("left join specbase sb on sb.specbaseid = ws.specbaseid ")
        sb.Append("left join spec s on s.specid = nvl(sb.revofrcdid,ws.specid) ")
        sb.Append("left join specbase sb1 on sb1.specbaseid = s.specbaseid ")
        sb.Append("LEFT JOIN cdodefinition cc ON cc.cdodefid=w.cdotypeid ")
        sb.Append(" LEFT JOIN containersplitinfo csi ON csi.containerid=c.splitfromid ") 'add:Wangjh 2017-7-26
        sb.Append(" LEFT JOIN workflowstep ws2 ON ws2.workflowstepid=csi.workflowstepid") 'add:Wangjh 2017-7-26

        sb.Append(" where c.containerid='" & strContainerid & "' and ws.sequence>= (CASE WHEN csi.workflowstepid IS NOT NULL THEN ws2.sequence ELSE 1 END) ") 'add:Wangjh 2017-7-26 增加工序筛选
        sb.Append(" GROUP BY c.containerid,pb.productname,w.workflowid ,wb.workflowname,ws.workflowstepname, sb1.specname,s.specid,s.iskey, ws.sequence,cc.cdoname,WS.SUBWORKFLOWID,WS.SUBWORKFLOWBASEID,s.opdesc,s.specno,WS.CONTROLCODE,ws.steptype")
        sb.Append(" order by ws.sequence")
        strSQL = sb.ToString()
        Return strSQL
    End Function

    Private Sub DeleteUnNeedReportContainerStep(ByRef ContainerStep As DataTable, ByVal QueryCondition As Dictionary(Of String, String))
        Dim strContainerid, strworkflowID As String
        strContainerid = ""
        strworkflowID = ""
        If QueryCondition.Keys.Contains("Containerid") Then
            strContainerid = QueryCondition("Containerid").ToString()
        End If
        If QueryCondition.Keys.Contains("workflowID") Then
            strworkflowID = QueryCondition("workflowID")
        End If
        Dim sb = New StringBuilder()
        sb.AppendLine("select wfs.workflowstepname,wfs.workflowid,wfs.sequence from movehistory m ")
        sb.AppendLine("left join historymainline hml on hml.historymainlineid=m.historymainlineid")
        sb.AppendLine("left join workflowstep wfs on wfs.workflowstepid=m.tostepid")
        sb.AppendLine("left join cdodefinition cdo on cdo.cdodefid=hml.txntype")
        sb.AppendLine("where  cdo.cdoname='MoveNonStd' and  hml.reversalstatus=1")
        sb.AppendFormat(" and m.historyid='{0}' and wfs.workflowid='{1}' order by txndate asc", strContainerid, strworkflowID)
        Dim dtTmp As DataTable = dal.GetQualityRecordInfoForProductNo(sb.ToString)
        If dtTmp.Rows.Count = 0 Then
            Return
        End If

        Dim dv As DataView = ContainerStep.DefaultView
        dv.RowFilter = String.Format("sequence>={0}", dtTmp.Rows(0)("sequence"))
        ContainerStep = dv.ToTable()
    End Sub

    Private Function GetSQL_ContainerStepByMfg(ByVal QueryCondition As Dictionary(Of String, String)) As String
        Dim strSQL = "", strContainerid As String
        strContainerid = ""
        If QueryCondition.Keys.Contains("Containerid") = True Then
            strContainerid = QueryCondition("Containerid").ToString()
        End If
        Dim sb = New StringBuilder()
        sb.Append("select c.containerid,pb.productname,w.workflowid ,wb.workflowname,ws.workflowstepname,sb1.specname,")
        sb.Append("s.specid,s.iskey, ws.sequence,cc.cdoname,WS.SUBWORKFLOWID,WS.SUBWORKFLOWBASEID,s.opdesc,s.specno,WS.CONTROLCODE,WS.steptype,B.BOMID from container c ")
        sb.Append("LEFT JOIN product p ON p.productid=c.productid ")
        sb.Append("LEFT JOIN productbase pb ON pb.productbaseid=p.productbaseid ")
        sb.Append("left join workflow w on w.workflowid = c.mfgworkflowid ")
        sb.Append("left join workflowbase wb on wb.workflowbaseid = w.workflowbaseid ")
        sb.Append("left join workflowstep ws on ws.workflowid = w.workflowid ")
        sb.Append("left join specbase sb on sb.specbaseid = ws.specbaseid ")
        sb.Append("left join spec s on s.specid = nvl(sb.revofrcdid,ws.specid) ")
        sb.Append("left join specbase sb1 on sb1.specbaseid = s.specbaseid ")
        sb.Append("LEFT JOIN cdodefinition cc ON cc.cdodefid=w.cdotypeid ")
        sb.AppendLine("LEFT JOIN BOM B ON B.PRODUCTID=P.PRODUCTID AND B.WORKFLOWID=W.WORKFLOWID ")
        sb.Append("where c.containerid='" & strContainerid & "' order by ws.sequence")
        strSQL = sb.ToString()
        Return strSQL
    End Function

    Private Function GetSQL_ContainerStepByHistoryMfg(ByVal QueryCondition As Dictionary(Of String, String)) As String
        Dim strSQL = "", strContainerid, strworkflowID As String
        strContainerid = ""
        strworkflowID = ""
        If QueryCondition.Keys.Contains("Containerid") Then
            strContainerid = QueryCondition("Containerid").ToString()
        End If
        If QueryCondition.Keys.Contains("workflowID") Then
            strworkflowID = QueryCondition("workflowID")
        End If
        Dim sb = New StringBuilder()
        sb.AppendLine(" select a.containerid,''productname,a.workflowid,a.workflowname,sb.specname workflowstepname,sb.specname,        ")
        sb.AppendLine("a.specid,s.iskey,0 sequence,''cdoname,ws.CONTROLCODE from ( ")

        ' sb.AppendLine(" select wcd.ContainerID,wcd.specid,wrh.qty,wf.workflowid,wfb.workflowname,em.fullname,hm.txndate ") update:Wangjh 2017-7-5 注释
        ' sb.AppendLine(" from WorkCenterDispatching wcd ")
        ' sb.AppendLine("inner join Workingreporthistory wrh on wrh.workcenterdispatchingid = wcd.workcenterdispatchingid")
        ' sb.AppendLine(" left join historymainline hm on hm.historymainlineid=wrh.historymainlineid")
        ' sb.AppendLine(" left join Employee em on em.employeeid = wrh.reportemployeeid")
        ' sb.AppendLine("left join workflowstep wfs on wfs.workflowstepid=hm.workflowstepid ")
        ' sb.AppendLine("left join workflow wf on wf.workflowid = wfs.workflowid ")
        ' sb.AppendLine("left join workflowbase wfb on wfb.workflowbaseid = wf.workflowbaseid ")
        ' sb.AppendLine(" left join speclastcheckhistory slch on slch.historyid = wcd.containerid")
        'sb.AppendLine("  left join historymainline hml1 on hml1.historymainlineid=slch.historymainlineid and hml1.workflowstepid=wfs.workflowstepid ")
        ' sb.AppendFormat(" where wcd.ContainerID='{0}' and hml1.historymainlineid is not null", strContainerid)

        sb.AppendLine("  SELECT csr.ContainerID,csr.specid,csr.ReportQty,csr.workflowid,csr.workflowname,csr.ReportEmployeeName,csr.reportdate,csr.workflowstepid FROM containersteprecord csr") 'create:Wangjh 2017-7-5
        sb.AppendFormat(" where CSR.ContainerID='{0}' and csr.usecompleted=1 AND csr.usefulstatus=1  ", strContainerid) 'create:Wangjh 2017-7-5

        'sb.AppendLine(" union all")
        'sb.AppendLine(" select sy.containerid,sy.specid,sy.qty,wf.workflowid,wfb.workflowname,em.fullname,sy.returndate txndate from synergicinfo sy")
        'sb.AppendLine(" left join Employee em on em.employeeid = sy.returnemployeeid")
        'sb.AppendLine("left join workflow wf on wf.workflowid = sy.workflowid ")
        'sb.AppendLine("left join workflowbase wfb on wfb.workflowbaseid = wf.workflowbaseid ")
        'sb.AppendFormat(" where sy.containerid='{0}' and sy.returnqty is not null)a ", strContainerid)
        sb.AppendLine(" ) a")
        sb.AppendLine("left join spec s on s.specid = a.specid ")
        sb.AppendLine("left join specbase sb on sb.specbaseid = s.specbaseid ")
        sb.AppendLine("left join workflowstep ws on ws.workflowstepid=a.workflowstepid")
        sb.AppendFormat(" where a.workflowid<>'{0}' order by reportdate asc", strworkflowID) 'update:Wangjh 2017-7-5 把txtDate改为reportdate
        strSQL = sb.ToString()
        Return strSQL
    End Function
    ''' <summary>
    ''' 获取批次的主子工艺
    ''' add:Wangjh 20180424
    ''' </summary>
    ''' <param name="QueryCondition"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function GetSQL_ContainerSubWorkflow(ByVal QueryCondition As Dictionary(Of String, String)) As DataTable
        Dim strSql As String = "SELECT w.Process_No,swc.* FROM subworkflowcheckinfo swc "
        strSql += " LEFT JOIN Workflow w ON w.workflowid=swc.subworkflowid"
        strSql += String.Format(" WHERE swc.containerid='{0}' order by swc.workflowid desc", QueryCondition("Containerid").ToString())
        Return DatabaseHelper.Query_ReturnDatTable(strSql)

    End Function

    Private Function GetSQL_WorkingReportStep(ByVal QueryCondition As Dictionary(Of String, String)) As String
        Dim strSQL = "", strContainerid As String
        strContainerid = ""
        If QueryCondition.Keys.Contains("Containerid") = True Then
            strContainerid = QueryCondition("Containerid").ToString()
        End If

        Dim sb = New StringBuilder()
        sb.Append(" select * from (")

        'sb.Append(" select wcd.ContainerID,wcd.specid,wrh.qty,em.fullname,hm.txndate")
        'sb.Append(" from WorkCenterDispatching wcd")
        'sb.Append(" inner join Workingreporthistory wrh on wrh.workcenterdispatchingid = wcd.workcenterdispatchingid")
        'sb.Append(" left join historymainline hm on hm.historymainlineid=wrh.historymainlineid")
        'sb.Append(" left join Employee em on em.employeeid = wrh.reportemployeeid")
        ' sb.Append(" where wcd.ContainerID='" + strContainerid + "'") 'update:Wangjh 2017-7-5 注释

        sb.AppendLine(" SELECT csr.containerid,csr.specid,csr.ReportQty as qty,csr.ReportEmployeeName,csr.reportdate,csw.checktype,ws.CONTROLCODE FROM containersteprecord csr") 'create:Wangjh 2017-7-5 
        '‘and csw.isuse = 0’ add by tianFW 
        sb.AppendLine(" left join containerstepworkreport csw on csw.containerstepworkreportid=csr.containersteprecordid and csw.isuse = 0")
        sb.AppendLine(" left join spec s ON s.specid=CSR.SPECID")
        sb.AppendLine(" LEFT JOIN WORKFLOWSTEP WS ON WS.WORKFLOWSTEPID=CSR.WORKFLOWSTEPID")
        sb.Append("where csr.Reportdate IS NOT NULL AND csr.usefulstatus=1 and csr.containerid='" + strContainerid + "'") 'create:Wangjh 2017-7-5 
        sb.AppendLine(" union all")
        '增加结构件转工 add:Wangjh 20171030
        sb.AppendLine(" SELECT csre.containerid,s.specid,csre.acceptanceqty as qty,e.fullname ReportEmployeeName,csre.recivedate reportdate,null checktype,WS3.CONTROLCODE FROM containershiprecive csre")
        sb.AppendLine(" LEFT JOIN containershiprecord csro ON csro.containershiprecordid=csre.containershiprecordid")
        sb.AppendLine(" LEFT JOIN Workflowstep ws ON ws.workflowstepid=csro.workflowstepid")
        sb.AppendLine(" LEFT JOIN workflow w ON w.workflowid=ws.workflowid")
        sb.AppendLine(" LEFT JOIN workflowbase wb ON wb.workflowbaseid=w.workflowbaseid")
        sb.AppendLine(" LEFT JOIN workflowstep ws2 ON ws2.workflowstepid=csro.toworkflowstepid")
        sb.AppendLine(" LEFT JOIN workflowstep ws3 ON ws3.workflowid=w.workflowid AND (ws3.sequence>=ws.sequence AND ws3.sequence<=ws2.sequence)")
        sb.AppendLine(" LEFT JOIN specbase sb ON sb.specbaseid=ws3.specbaseid")
        sb.AppendLine(" LEFT JOIN spec s ON s.specid=NVL(sb.revofrcdid,ws3.specid)")
        sb.AppendLine(" LEFT JOIN employee e ON e.employeeid=csre.reciver")
        sb.AppendFormat(" WHERE csre.reciver IS NOT NULL AND csre.containerid='{0}'", strContainerid)
        '
        'sb.Append(" union all")
        'sb.Append(" select sy.containerid,sy.specid,sy.qty,em.fullname,sy.returndate reportdate from synergicinfo sy")
        'sb.Append(" left join Employee em on em.employeeid = sy.returnemployeeid")
        'sb.Append(" where sy.containerid='" + strContainerid + "' and sy.returnqty is not null")
        sb.Append(" )a order by reportdate desc") 'update:Wangjh 2017-7-5 把txndate改为reportdate

        strSQL = sb.ToString()
        Return strSQL
    End Function

    Private Function GetSQL_QualityResult(ByVal QueryCondition As Dictionary(Of String, String)) As String
        Dim strSQL = "", strContainerid As String
        strContainerid = ""
        If QueryCondition.Keys.Contains("Containerid") = True Then
            strContainerid = QueryCondition("Containerid").ToString()
        End If

        strSQL = "SELECT q.Specid,q.qty,em.fullname,sb.Specname, q.Qualityrecordname, pi.Productno, pi.Qualityrecordnotes, " & vbCrLf & _
                 "       qd.Qualitydisposeadvicename, pi.Qualitydisposenotes,NULL lossqty,NULL defectqty,S.CONTROLCODE" & vbCrLf & _
                 "FROM Qualityrecord q" & vbCrLf & _
                 "left JOIN Spec s ON q.Specid = s.Specid" & vbCrLf & _
                 "left JOIN Specbase sb ON s.Specbaseid = sb.Specbaseid" & vbCrLf & _
                 "left JOIN Qualityrecordproductnoinfo qp ON q.Qualityrecordid = qp.Qualityrecordid" & vbCrLf & _
                 "left JOIN Productnoinfo  pi ON qp.Productnoinfoid = pi.Productnoinfoid " & vbCrLf & _
                 "left JOIN QualityDisposeAdvice qd ON qd.Qualitydisposeadviceid = pi.Qualitydisposeadviceid" & vbCrLf & _
                 "left join employee em on em.employeeid =q.checkemployeeid" & vbCrLf & _
                 "WHERE q.checkvalidatedate is not  null and  q.ContainerID = '" & strContainerid & "' " & vbCrLf
        'jgj add:Wangjh 2017-7-26
        strSQL += " UNION ALL "
        strSQL += " SELECT csr.specid,NULL qty,csr.checkemployeename fullname,sb.specname,NULL Qualityrecordname,NULL Productno,NULL Qualityrecordnotes," &
                    " NULL Qualitydisposeadvicename,NULL Qualitydisposenotes ,csr.lossqty,csr.defectqty,WS.CONTROLCODE FROM containersteprecord csr" &
                    " LEFT JOIN spec s ON s.specid=csr.specid" &
                    " LEFT JOIN specbase sb ON sb.specbaseid=s.specbaseid" &
                    " LEFT JOIN WORKFLOWSTEP WS ON WS.WORKFLOWSTEPID=CSR.WORKFLOWSTEPID" &
                    " WHERE csr.containerid='" & strContainerid & "'"
        '增加转工 转回人 
        strSQL += " UNION ALL "
        strSQL += " SELECT s.specid,NULL qty,e.fullname,ws.workflowstepname specname,NULL Qualityrecordname,NULL Productno,NULL Qualityrecordnotes,NULL Qualitydisposeadvicename,NULL Qualitydisposenotes,csre.lossqty,csre.defectqty,WS3.CONTROLCODE  FROM containershiprecive csre" &
                   "  LEFT JOIN containershiprecord csro ON csro.containershiprecordid=csre.containershiprecordid" &
                   "  LEFT JOIN Workflowstep ws ON ws.workflowstepid=csro.workflowstepid" &
                    " LEFT JOIN workflow w ON w.workflowid=ws.workflowid" &
                    " LEFT JOIN workflowbase wb ON wb.workflowbaseid=w.workflowbaseid" &
                   "  LEFT JOIN workflowstep ws2 ON ws2.workflowstepid=csro.toworkflowstepid" &
                   "  LEFT JOIN workflowstep ws3 ON ws3.workflowid=w.workflowid AND (ws3.sequence>=ws.sequence AND ws3.sequence<=ws2.sequence)" &
                   "  LEFT JOIN specbase sb ON sb.specbaseid=ws3.specbaseid" &
                   "  LEFT JOIN spec s ON s.specid=NVL(sb.revofrcdid,ws3.specid)" &
                   "  LEFT JOIN employee e ON e.employeeid=csre.reciver" &
                    "  WHERE csre.reciver Is Not NULL And csre.containerid='" & strContainerid & "' "
        Return strSQL
    End Function

#Region "得到过程检验备注信息"
    Private Function GetConventCheckInfo(ByVal QueryCondition As Dictionary(Of String, String)) As DataTable
        Dim strSQL As String = ""
        Dim strContainerid As String = ""
        If QueryCondition.Keys.Contains("Containerid") = True Then
            strContainerid = QueryCondition("Containerid").ToString()
        End If
        'strSQL = "SELECT t.notes,h.containerid,h.specid,h.workflowstepid, ( CASE WHEN  wt.specid='0000000000000000' THEN sb.revofrcdid  ELSE  wt.specid END) AS specid1 FROM conventioncheckhistory t " & vbCrLf & _
        '         "LEFT JOIN historymainline h ON h.historymainlineid=t.historymainlineid " & vbCrLf & _
        '         "LEFT JOIN Workflowstep wt ON wt.workflowstepid=h.workflowstepid " & vbCrLf & _
        '         "LEFT JOIN specbase sb ON sb.specbaseid=wt.specbaseid " & vbCrLf & _
        '         "WHERE h.containerid='" & strContainerid & "'"  'update:Wangjh 2017-7-14 注释原来的语句

        strSQL = <string> 
                     SELECT cch.notes,csr.containerid,csr.specid,csr.workflowstepid FROM containersteprecord csr 
                    INNER JOIN WORKINGREPORTHISTORY wrh ON wrh.historymainlineid=csr.containersteprecordid
                    INNER JOIN conventioncheckhistory cch ON cch.workingreportid=wrh.WorkingReportHistoryId
                 </string>
        strSQL += String.Format(" WHERE csr.containerid='{0}' AND csr.usefulstatus=1", strContainerid) 'create:Wangjh 2017-7-14
        'jgj
        Dim dtTmp As DataTable = dal.GetQualityRecordInfoForProductNo(strSQL)
        dtTmp.TableName = "conventioncheck"
        Return dtTmp
    End Function
#End Region

    Private Function GetSQL_SpecLastCheck(ByVal QueryCondition As Dictionary(Of String, String)) As String
        Dim strSQL = "", strContainerid As String
        strContainerid = ""
        If QueryCondition.Keys.Contains("Containerid") = True Then
            strContainerid = QueryCondition("Containerid").ToString()
        End If

        'strSQL = "select hm.containerid,NVL(s.specid,sb.revofrcdid)specid,em.fullname,l.notes " & vbCrLf & _
        '         " from speclastcheckhistory l " & vbCrLf & _
        '         "inner join  historymainline hm on hm.historymainlineid = l.historymainlineid" & vbCrLf & _
        '         "inner join workflowstep ws on ws.workflowstepid= hm.workflowstepid" & vbCrLf & _
        '         "left join specbase sb on sb.specbaseid=ws.specbaseid" & vbCrLf & _
        '         "left join spec s on s.specid = ws.specid" & vbCrLf & _
        '         "left join employee em on em.employeeid=l.reportemployeeid" & vbCrLf & _
        '         "WHERE hm.containerid = '" & strContainerid & "' " & vbCrLf

        strSQL = " SELECT csr.containerid,csr.specid,csr.reportemployeename AS fullname,null notes,WS.CONTROLCODE FROM containersteprecord csr" 'create:Wangjh 2017-7-5 弃用更慢
        strSQL += " left join WORKFLOWSTEP WS on WS.WORKFLOWSTEPID=csr.WORKFLOWSTEPID"
        strSQL += String.Format(" WHERE csr.usecompleted=1 AND csr.usefulstatus=1 AND csr.containerid='{0}' ", strContainerid)

        Return strSQL
    End Function
    ''' <summary>
    ''' modify:wangjh 20230529，将SC.CHECKPOINTNOTE---->SC.drawingrequire
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function GetSQL_StepCheckItemList2(ByVal para As Dictionary(Of String, String)) As String
        Dim strSQL =
            <string>
                SELECT st.checkpointresultnotedis,st.workcheckpointresultnotedis,st.workcheckpointresultnote,st.checkpointresultnote,SC.NAME CHECKPOINTGROUPNAME,s.SPECID, SC.drawingrequire CHECKPOINTNOTE,
                SC.NAME DESCRIPTION, '' NOTES, '' CHECKERRESULTINFO,WFS.CONTROLCODE,SC.ASSEMBLYTYPE,SC.CHECKNO,SC.Checktype,SC.DrawingRequire,
                SC.EMPLOYEEID,SC.INPUTNUM,SC.INTIME,SC.KEYCHECK,SC.MeopCheck,SC.ProductID,SC.QualityCheck,SC.StepCheckPointID,SC.StepInfoID,
                SC.TCCheckPointNote,SC.WorkflowStepID
              FROM STEPCHECKPOINT SC
                left join stepcheckpointresult st on st.stepcheckpointid=sc.stepcheckpointid and st.containerid='{0}'
              LEFT JOIN WORKFLOWSTEP WFS ON SC.WORKFLOWSTEPID = WFS.WORKFLOWSTEPID
              LEFT JOIN SPECBASE SB  ON WFS.SPECBASEID = SB.SPECBASEID
             left join spec s on s.specid=NVL(SB.REVOFRCDID, WFS.SPECID)
            </string>.Value
        Dim isQueryResult = True
        If para.ContainsKey("IsBlank") Then
            If para("IsBlank") = "0" Then '空白卡不打结果
                isQueryResult = False
            End If
        End If
        If para.ContainsKey("Containerid") And isQueryResult Then
            strSQL = String.Format(strSQL, para("Containerid"))
        End If
        GetSQL_StepCheckItemList2 = strSQL + String.Format(" WHERE sc.KeyCheck=1  AND WFS.WORKFLOWID in ({0})", para("WorkFlowIDs"))

        GetSQL_StepCheckItemList2 += " GROUP BY st.checkpointresultnotedis,st.workcheckpointresultnotedis,st.workcheckpointresultnote,SC.NAME,s.SPECID, SC.CHECKPOINTNOTE,WFS.CONTROLCODE,SC.ASSEMBLYTYPE,SC.CHECKNO,SC.Checktype,SC.DrawingRequire," &
                "SC.EMPLOYEEID,SC.INPUTNUM,SC.INTIME,SC.KEYCHECK,SC.MeopCheck,SC.ProductID,SC.QualityCheck,SC.StepCheckPointID,SC.StepInfoID," &
               " SC.TCCheckPointNote,SC.WorkflowStepID,st.checkpointresultnote "
    End Function

    Function GetSQL_StepCheckItemList(ByVal para As Dictionary(Of String, String)) As String
        Dim strSQL =
            <string>
                SELECT st.checkpointresultnotedis,st.workcheckpointresultnotedis,st.workcheckpointresultnote,st.checkpointresultnote,SC.NAME CHECKPOINTGROUPNAME,s.SPECID, SC.CHECKPOINTNOTE,
                SC.NAME DESCRIPTION, '' NOTES, '' CHECKERRESULTINFO,WFS.CONTROLCODE,SC.ASSEMBLYTYPE,SC.CHECKNO,SC.Checktype,SC.DrawingRequire,
                SC.EMPLOYEEID,SC.INPUTNUM,SC.INTIME,SC.KEYCHECK,SC.MeopCheck,SC.ProductID,SC.QualityCheck,SC.StepCheckPointID,SC.StepInfoID,
                SC.TCCheckPointNote,SC.WorkflowStepID
              FROM STEPCHECKPOINT SC
                left join stepcheckpointresult st on st.stepcheckpointid=sc.stepcheckpointid and st.containerid='{0}'
              LEFT JOIN WORKFLOWSTEP WFS ON SC.WORKFLOWSTEPID = WFS.WORKFLOWSTEPID
              LEFT JOIN SPECBASE SB  ON WFS.SPECBASEID = SB.SPECBASEID
             left join spec s on s.specid=NVL(SB.REVOFRCDID, WFS.SPECID)
            </string>.Value
        If para.ContainsKey("Containerid") Then
            strSQL = String.Format(strSQL, para("Containerid"))
        End If
        GetSQL_StepCheckItemList = strSQL + String.Format(" WHERE sc.KeyCheck=1  AND WFS.WORKFLOWID='{0}'", para("WorkFlowID"))

        GetSQL_StepCheckItemList += " GROUP BY st.checkpointresultnotedis,st.workcheckpointresultnotedis,st.workcheckpointresultnote,SC.NAME,s.SPECID, SC.CHECKPOINTNOTE,WFS.CONTROLCODE,SC.ASSEMBLYTYPE,SC.CHECKNO,SC.Checktype,SC.DrawingRequire," &
                "SC.EMPLOYEEID,SC.INPUTNUM,SC.INTIME,SC.KEYCHECK,SC.MeopCheck,SC.ProductID,SC.QualityCheck,SC.StepCheckPointID,SC.StepInfoID," &
               " SC.TCCheckPointNote,SC.WorkflowStepID,st.checkpointresultnote "
    End Function

    Function GetSQL_CheckItemList(ByVal QueryCondition As Dictionary(Of String, String)) As String
        Dim strSQL = "", strContainerid As String
        strContainerid = ""
        If QueryCondition.Keys.Contains("Containerid") = True Then
            strContainerid = QueryCondition("Containerid").ToString()
        End If

        strSQL = "SELECT  Checkpointgroup.Checkpointgroupname,Checkpointgroup.specid, " & vbCrLf & _
                 "Checkpoints.Checkpointnote,Checkpoints.description,Checkpointgroup.Notes,Checkpointgroup.Description,checkpointresultdetail.checkerresultinfo,null checkno" & vbCrLf & _
                 "from Checkpointgroup" & vbCrLf & _
                 "INNER JOIN  container on container.productid = Checkpointgroup.Productid" & vbCrLf & _
                 "INNER JOIN Checkpointgroupcheckpoints ON Checkpointgroupcheckpoints.Checkpointgroupid = Checkpointgroup.Checkpointgroupid" & vbCrLf & _
                 "INNER JOIN Checkpoints ON Checkpointgroupcheckpoints.Checkpointsid = Checkpoints.Checkpointsid" & vbCrLf & _
                "LEFT JOIN checkpointresult on checkpointresult.containerid = container.containerid" & vbCrLf & _
                "LEFT JOIN checkpointresultdetail on checkpointresultdetail.checkpoints = Checkpoints.Checkpointsid and checkpointresult.checkpointresultid = checkpointresult.checkpointresultid" & vbCrLf & _
                 "WHERE container.containerid = '" & strContainerid & "' " & vbCrLf
        Return strSQL
    End Function

    Function GetSQL_BOM(ByVal strBomID As String, ByVal strworkflow As String, ByVal productID As String) As String
        'Modify by Yuxj20200528 物料描述根据 主数据表查询
        GetSQL_BOM = <STRING>
                        SELECT * FROM (  
                        SELECT PB1.productname,jsm.t_wlms description  ,SB1.specname, pm.productbaseid,
                               pm.productid,pm.specbaseid,s.specid,pm.qtyrequired,b.bomid,s.opdesc,s.specno,PM.MATERIALTXNLOGIC,pm.referencedesignator,
                                CASE WHEN pm.print_status = 1 THEN '限制使用' ELSE NULL END bzinfo 
                        FROM bom b 
                        LEFT JOIN productmateriallistitem pm ON pm.bomid=b.bomid 
                        LEFT JOIN productbase pb ON pb.productbaseid=pM.productbaseid 
                        LEFT JOIN PRODUCT P ON P.PRODUCTID=NVL(PB.REVOFRCDID,PM.PRODUCTID)
                        LEFT JOIN PRODUCTBASE PB1 ON PB1.PRODUCTBASEID=P.PRODUCTBASEID
                        left join jbst_sync_mes jsm on jsm.t_code=nvl(p.materialcode,pb1.productname)
                        LEFT JOIN SPECBASE SB ON SB.SPECBASEID=PM.SPECBASEID
                        LEFT JOIN SPEC S ON S.SPECID=NVL(SB.REVOFRCDID,PM.SPECID)
                        LEFT JOIN SPECBASE SB1 ON SB1.SPECBASEID=S.SPECBASEID 
                       -- LEFT JOIN WORKFLOWSTEP WS ON WS.SPECID=S.SPECID OR WS.SPECBASEID=S.SPECBASEID
                    </STRING>
        GetSQL_BOM += String.Format(" WHERE b.bomid='{0}' and b.workflowid='{1}' ", strBomID, strworkflow)
        GetSQL_BOM += " group by PB1.productname ,jsm.t_wlms ,SB1.specname, pm.productbaseid," &
                       "  pm.productid,pm.specbaseid,s.specid,pm.qtyrequired,b.bomid,s.opdesc,s.specno,PM.MATERIALTXNLOGIC,pm.referencedesignator,pm.print_status" 'modify zhunan 2021.7.27 增加【,pm.print_status】 
        'modify zhunan 2021.7.27 增加【,NULL bzinfo】
        GetSQL_BOM += <string>
                         UNION ALL
					    SELECT p.materialrequest productname,NULL description,NULL specname,NULL productbaseid,NULL productid,
					    NULL specbaseid,NULL specid,1 qtyrequired,NULL bomid,NULL opdesc,'0005' specno, NULL MATERIALTXNLOGIC,null referencedesignator,NULL bzinfo FROM product p 
					    LEFT JOIN materialtype mt ON mt.materialtypeid=p.materialtypeid
					    WHERE (mt.materialtypename='管材' or mt.materialtypename='自制管坯件') and p.materialrequest is not null
                     </string>.Value
        GetSQL_BOM += " AND P.productid='" + productID + "')"
        GetSQL_BOM += " order by specno asc,productname"
    End Function

    ''' <summary>
    ''' 查询批次配套表信息（重载方法）
    ''' add zhunan 2022.1.18
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function GetSQL_BOM(ByVal para As Dictionary(Of String, String)) As String
        GetSQL_BOM = <STRING>
                        SELECT * FROM (  
                         SELECT distinct SB1.specname,s.specno,s.opdesc,s.specid,PB1.productname,jsm.t_wlms description  ,pm.yjwjh,pm.zynr,replace(pm.xzsy,chr(13),'\n') xzsy,pm.print_status,
PM.QTYREQUIRED,PM.PRODUCTMATERIALLISTITEMID,  null WORKFLOWSTEPID,null ISSUEQTY,null PRODUCTCHILDNO,null fullname,null NOTE,null ISSUEERPMATERIALID,
null WORKFLOWSTEPNAME,null BOMMATERIALLISTITEMID,null COLLECTERPMATERIALID,null ISSCRAP,null LOSSQTYSUM,
       pm.productid,null TOPRODUCTCHILDNO ,b.bomid,PM.MATERIALTXNLOGIC,pm.referencedesignator,
       decode(pm.print_status,1,'限制使用 ','') || to_char(cba.notes) bzinfo
                        FROM bom b 
                        LEFT JOIN productmateriallistitem pm ON pm.bomid=b.bomid 
                        LEFT JOIN productbase pb ON pb.productbaseid=pM.productbaseid 
                        LEFT JOIN PRODUCT P ON P.PRODUCTID=NVL(PB.REVOFRCDID,PM.PRODUCTID)
                        LEFT JOIN PRODUCTBASE PB1 ON PB1.PRODUCTBASEID=P.PRODUCTBASEID
                        left join jbst_sync_mes jsm on jsm.t_code=nvl(p.materialcode,pb1.productname)
                        LEFT JOIN SPECBASE SB ON SB.SPECBASEID=PM.SPECBASEID
                        LEFT JOIN SPEC S ON S.SPECID=NVL(SB.REVOFRCDID,PM.SPECID)
                        LEFT JOIN SPECBASE SB1 ON SB1.SPECBASEID=S.SPECBASEID
                        LEFT JOIN (
                             SELECT cba.productid,wm_concat(cba.notes) notes
                             FROM containerbomproductattr cba
                             WHERE cba.containerid = '{0}'
                             GROUP BY cba.productid
                        ) cba ON cba.productid = pm.productid
                    </STRING>

        GetSQL_BOM = String.Format(GetSQL_BOM, para("containerid"))
        GetSQL_BOM += String.Format(" WHERE b.bomid='{0}' and b.workflowid='{1}' ", para("bomid"), para("workflowid"))

        GetSQL_BOM += <string>
                        UNION ALL
					   SELECT null specname,'0005' specno, null opdesc,null specid,p.materialrequest productname,
NULL description,null yjwjh,null zynr,null xzsy,null print_status,
1 QTYREQUIRED,null PRODUCTMATERIALLISTITEMID,  null WORKFLOWSTEPID,null ISSUEQTY,null PRODUCTCHILDNO,null fullname,
null NOTE,null ISSUEERPMATERIALID,null WORKFLOWSTEPNAME,null BOMMATERIALLISTITEMID,null COLLECTERPMATERIALID,null ISSCRAP,
null LOSSQTYSUM,null productid,null TOPRODUCTCHILDNO ,null bomid,null MATERIALTXNLOGIC,null referencedesignator,null bzinfo
                        FROM product p 
					    LEFT JOIN materialtype mt ON mt.materialtypeid=p.materialtypeid
					    WHERE (mt.materialtypename='管材' or  mt.materialtypename='自制管坯件') and p.materialrequest is not null
                     </string>.Value
        GetSQL_BOM += " AND P.productid='" + para("productid") + "')"
        GetSQL_BOM += " order by specno asc,productname"
    End Function

    ''' <summary>
    ''' 获取集件批次集件信息
    ''' create:Wangjh 20180109
    ''' modify zhunan 2021.7.28
    ''' 于20230302 弃用
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Protected Function GetCollectPieceManageBOMList(ByVal para As Dictionary(Of String, String)) As DataTable
        Dim strSql As New StringBuilder
        'strSql.AppendLine("SELECT S.SPECNO,s.specid, PBN.PRODUCTNAME, PML.QTYREQUIRED,PML.PRODUCTMATERIALLISTITEMID,WS.WORKFLOWSTEPID," &
        'add zhunan 2021.7.28 增加【,NVL(jb.t_wlms,p.description) description,pml.yjwjh,pml.zynr,replace(pml.xzsy,chr(13),'\n') xzsy,pml.print_status】
        strSql.AppendLine("SELECT S.SPECNO,s.specid,PBN.PRODUCTNAME,NVL(jb.t_wlms,p.description) description,pml.yjwjh,pml.zynr,replace(pml.xzsy,chr(13),'\n') xzsy,pml.print_status,PML.QTYREQUIRED,PML.PRODUCTMATERIALLISTITEMID,WS.WORKFLOWSTEPID," &
                          "IEM.ISSUEQTY ISSUEQTY,IEM.PRODUCTCHILDNO,E.fullname,IEM.NOTE,IEM.ISSUEERPMATERIALID,WS.WORKFLOWSTEPNAME," &
                          "IEM.BOMMATERIALLISTITEMID,IEM.COLLECTERPMATERIALID,LSQS.ISSCRAP,LSQS.LOSSQTYSUM,PML.PRODUCTID,IEM2.PRODUCTCHILDNO TOPRODUCTCHILDNO FROM PRODUCTMATERIALLISTITEM PML")
        strSql.AppendLine(" LEFT JOIN PRODUCTBASE PB ON PB.PRODUCTBASEID = PML.PRODUCTBASEID")
        strSql.AppendLine(" LEFT JOIN PRODUCT P ON P.PRODUCTID =NVL(PB.REVOFRCDID, PML.PRODUCTID)")
        strSql.AppendLine(" LEFT JOIN PRODUCTBASE PBN ON PBN.PRODUCTBASEID=P.PRODUCTBASEID")
        strSql.AppendLine(" LEFT JOIN jbst_sync_mes jb ON jb.t_code = pbn.productname") 'add zhunan 2021.7.28
        strSql.AppendLine(" LEFT JOIN SPECBASE SB ON SB.SPECBASEID=PML.SPECBASEID")
        strSql.AppendLine("  LEFT JOIN SPEC S ON  S.SPECID=NVL(SB.REVOFRCDID, PML.SPECID)")
        strSql.AppendFormat(" LEFT JOIN ISSUEERPMATERIAL IEM ON IEM.BOMMATERIALLISTITEMID=PML.PRODUCTMATERIALLISTITEMID AND IEM.CONTAINERID='{0}'", para("ContainerID"))
        strSql.AppendLine(" LEFT JOIN EMPLOYEE E ON IEM.EMPLOYEEID=E.EMPLOYEEID")
        strSql.AppendLine(" left join specbase sb2 on sb2.REVOFRCDID=s.specid") '工序选择默认版本情况
        strSql.AppendLine("  LEFT JOIN WORKFLOWSTEP WS ON (WS.SPECBASEID=PML.SPECBASEID AND PML.SPECID='0000000000000000') OR (WS.SPECID=PML.SPECID AND PML.SPECBASEID='0000000000000000') OR (WS.SPECBASEID=SB2.SPECBASEID AND WS.SPECID='0000000000000000')")
        strSql.AppendLine(" LEFT JOIN (SELECT SUM(IEMS.LOSSQTY) LOSSQTYSUM,COUNT(*) ISSCRAP,IssueERPMaterialID FROM IssueERPMaterialScrapInfo IEMS" &
                          " WHERE IEMS.CheckerID IS NOT NULL" &
                            " GROUP BY IssueERPMaterialID) LSQS" &
                            " ON LSQS.IssueERPMaterialID=IEM.ISSUEERPMATERIALID")
        strSql.AppendLine("  LEFT JOIN ISSUEERPMATERIAL IEM2 ON IEM2.ISSUEERPMATERIALID=IEM.TOISSUEERPMATERIALID")
        strSql.AppendFormat(" WHERE PML.BOMID = '{0}' AND WS.WORKFLOWID='{1}'", para("BomID"), para("WorkFlowID"))

        If para.ContainsKey("FactoryID") Then
            strSql.AppendFormat(" AND S.FACTORYID='{0}'", para("FactoryID"))
        End If
        strSql.AppendLine(" ORDER BY S.SPECNO")
        GetCollectPieceManageBOMList = DatabaseHelper.Query_ReturnDatTable(strSql.ToString)
    End Function

    ''' <summary>
    ''' 获取批次已集件信息
    ''' add:wangjh 20230302
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function GetContainerIssueInfo(ByVal para As Dictionary(Of String, String)) As DataTable
        Dim strSql = <string>select sb2.specname,S.SPECNO,s.opdesc,s.specid,PBN.PRODUCTNAME,NVL(jb.t_wlms,p.description) description,
pml.yjwjh,pml.zynr,replace(pml.xzsy,chr(13),'\n') xzsy,pml.print_status,
PML.QTYREQUIRED,PML.PRODUCTMATERIALLISTITEMID,WS.WORKFLOWSTEPID,
il.ISSUEQTY ISSUEQTY,il.PRODUCTCHILDNO,E.fullname,
il.NOTE,il.ISSUEERPMATERIALID,WS.WORKFLOWSTEPNAME,il.BOMMATERIALLISTITEMID,il.COLLECTERPMATERIALID,
LSQS.ISSCRAP,LSQS.LOSSQTYSUM,PML.PRODUCTID,IEM2.PRODUCTCHILDNO TOPRODUCTCHILDNO,pml.bomid,PMl.MATERIALTXNLOGIC,PMl.referencedesignator,
decode(PMl.print_status,1,'限制使用 ','') || to_char(cba.notes) bzinfo from issueerpmaterial il
left join PRODUCTMATERIALLISTITEM pml on pml.productmateriallistitemid=il.bommateriallistitemid
LEFT JOIN PRODUCTBASE PB ON PB.PRODUCTBASEID = PML.PRODUCTBASEID
LEFT JOIN PRODUCT P ON P.PRODUCTID =NVL(PB.REVOFRCDID, PML.PRODUCTID)
LEFT JOIN PRODUCTBASE PBN ON PBN.PRODUCTBASEID=P.PRODUCTBASEID
LEFT JOIN jbst_sync_mes jb ON jb.t_code = pbn.productname
LEFT JOIN SPECBASE SB ON SB.SPECBASEID=PML.SPECBASEID
LEFT JOIN SPEC S ON  S.SPECID=NVL(SB.REVOFRCDID, PML.SPECID)
left join specbase sb2 on sb2.REVOFRCDID=s.specid
LEFT JOIN EMPLOYEE E ON il.EMPLOYEEID=E.EMPLOYEEID
LEFT JOIN 
(
SELECT SUM(IEMS.LOSSQTY) LOSSQTYSUM,COUNT(*) ISSCRAP,IssueERPMaterialID FROM IssueERPMaterialScrapInfo IEMS 
WHERE IEMS.CheckerID IS NOT NULL GROUP BY IssueERPMaterialID
) LSQS ON LSQS.IssueERPMaterialID=il.ISSUEERPMATERIALID
 
LEFT JOIN (
   SELECT cba.productid,wm_concat(cba.notes) notes
   FROM containerbomproductattr cba
   WHERE cba.containerid = '{0}'
   GROUP BY cba.productid
) cba ON cba.productid = pml.productid

LEFT JOIN ISSUEERPMATERIAL IEM2 ON IEM2.ISSUEERPMATERIALID=il.TOISSUEERPMATERIALID
left join workflowstep ws on ws.workflowstepid=il.workflowstepid
where il.containerid='{0}'
ORDER BY s.specno,pbn.productname
</string>.Value

        Return DatabaseHelper.Query_ReturnDatTable(String.Format(strSql, para("ContainerID")))

    End Function

    ''' <summary>
    ''' 获取待打印的工序信息
    ''' add:Wangjh 20180509
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function GetWorkflowStepForPrint(ByVal para As Dictionary(Of String, String)) As String
        '增加 '' checknote add zhunan 2022.4.24
        Dim strSql As String = <string>
    SELECT w.workflowid,wb.workflowname,ws.workflowstepid,ws.workflowstepname,sb1.specname,s.specid,s.iskey,
            ws.sequence,ws.subworkflowid,ws.subworkflowbaseid,s.opdesc,nvl(s.specno,SUBSTR(ws.workflowstepname,0,INSTR(ws.workflowstepname,'-')-1)) specno,
            NVL(ws.controlcode,s.controlcode) controlcode,ws.steptype,s.lastcheck,'' checknote,'' worknote,0 SpecimenQty,0 SpecimenLossQty
FROM workflow w 
LEFT JOIN workflowbase wb ON wb.workflowbaseid=w.workflowbaseid
LEFT JOIN workflowstep ws ON ws.workflowid=w.workflowid
left join specbase sb on sb.specbaseid = ws.specbaseid 
left join spec s on s.specid = nvl(sb.revofrcdid,ws.specid) 
left join specbase sb1 on sb1.specbaseid = s.specbaseid 
WHERE (NVL(ws.controlcode,s.controlcode)!='LY07' OR NVL(ws.controlcode,s.controlcode) IS NULL) 
</string>.Value
        'strSql = String.Format(strSql, para("Containerid"))
        strSql += String.Format(" AND w.workflowid='{0}'", para("WorkFlowID"))
        If para.ContainsKey("Sequence") Then
            strSql += String.Format(" and ws.sequence>={0}", para("Sequence"))
        End If

        strSql += <string> group by w.workflowid,wb.workflowname,ws.workflowstepid,ws.workflowstepname,sb1.specname,s.specid,s.iskey,
ws.sequence,ws.subworkflowid,ws.subworkflowbaseid,s.opdesc,s.specno,ws.workflowstepname,ws.controlcode,s.controlcode,ws.steptype,s.lastcheck </string>.Value

        strSql += " ORDER BY ws.sequence ASC"
        GetWorkflowStepForPrint = strSql
    End Function


    ''' <summary>
    ''' 查询对应批次和工艺的加工记录
    ''' add：Wangjh 20180508
    ''' </summary>
    ''' <param name="containerID"></param>
    ''' <param name="workflowID"></param>
    ''' <param name="isTranfer"></param>
    ''' <returns></returns>
    ''' <remarks>and csw.isuse = 0  add by tianFW 2018年9月29日</remarks>
    Function GetContainerStepWorkingRecord(ByVal containerID As String, ByVal workflowID As String, ByVal isTranfer As Boolean, Optional ByVal conPrintType As String = "1") As String
        '增加 NVL(cfk.checknote,ck.checknote) checknote modify zhunan 2022.4.24
        '增加 worknote(工人报工备注) add by zhangrj 2022-07-19
        Dim strSql As String = <string>
SELECT csr.factoryid,csr.containerid,csr.workflowid,csr.workflowstepid,ws.WORKFLOWSTEPname,sb.specname,csr.specid,s.iskey,csr.ReportQty,
        csr.ReportEmployeeName,csw.containerstepworkreportid,
        (CASE WHEN csr.reportemployeeid IS NULL OR csr.reportemployeeid=csr.checkemployeeid THEN csr.checkdate ELSE csr.reportdate END) reportdate,
        csw.checktype,csr.checkemployeename,csr.passqty,csr.lossqty,csr.defectqty,csr.ReworkQty,csr.usecompleted,ws.sequence,s.opdesc,
        nvl(s.specno,SUBSTR(ws.workflowstepname,0,INSTR(ws.workflowstepname,'-')-1)) specno,s.lastcheck,NVL(ws.controlcode,s.controlcode) controlcode,
        NVL(cfk.checknote,ck.checknote) checknote,csw.notes worknote,csw.SpecimenQty,csw.SpecimenLossQty,wm_concat(distinct ci.notes) notes,0 ISCOMMISSION
FROM containersteprecord csr 
left join containerstepworkreport csw on csw.containerstepworkreportid=csr.containersteprecordid and csw.isuse = 0 
left join spec s ON s.specid=CSR.SPECID
left join specbase sb on sb.specbaseid=s.specbaseid
LEFT JOIN WORKFLOWSTEP WS ON WS.WORKFLOWSTEPID=CSR.WORKFLOWSTEPID
LEFT JOIN Containercheckinfo ck ON ck.containerstepworkreportid = csw.containerstepworkreportid AND ck.isuse = 0
LEFT JOIN containerfinishedcheckinfo cfk ON cfk.containerid = csw.containerid AND cfk.workflowstepid = csw.workflowstepid AND cfk.isuse = 0 
left join containerstepattribute ci on ci.ContainerID=csr.ContainerID and ci.WorkflowStepID=csr.WorkflowStepID and ci.notes is not null                                  
WHERE (NVL(ws.controlcode,s.controlcode)!='LY07' OR NVL(ws.controlcode,s.controlcode) IS NULL) AND csr.Reportdate IS NOT NULL AND csr.usefulstatus=1 
                               </string>
        strSql += String.Format(" and csr.containerid='{0}'", containerID)
        strSql += <string> group by csr.factoryid,csr.containerid,csr.workflowid,csr.workflowstepid,ws.WORKFLOWSTEPname,sb.specname,csr.specid,s.iskey,csr.ReportQty,
        csr.ReportEmployeeName,csw.containerstepworkreportid,csr.reportemployeeid,csw.checktype,csr.checkemployeename,csr.passqty,csr.lossqty,csr.defectqty,csr.ReworkQty,csr.usecompleted,ws.sequence,s.opdesc,
        s.specno,ws.workflowstepname,s.lastcheck,ws.controlcode,s.controlcode,cfk.checknote,ck.checknote,csw.notes,csw.SpecimenQty,csw.SpecimenLossQty,csr.checkemployeeid,csr.checkdate,csr.reportdate</string>.Value
        If conPrintType = "1" Then
            'add:Wangjh 20221013 批次备注也算加工记录
            strSql += <string> 
        union all
        select null factoryid,ci.containerid,ws.workflowid,ws.workflowstepid,ws.workflowstepname,sb2.specname,s.specid,s.iskey,null ReportQty,null ReportEmployeeName,
        null containerstepworkreportid,null reportdate,null checktype,null checkemployeename,null passqty,null lossqty,null defectqty,null ReworkQty,null usecompleted, ws.sequence,s.opdesc,
        s.specno,s.lastcheck,NVL(ws.controlcode,s.controlcode) controlcode,null checknote,null worknote,null SpecimenQty,null SpecimenLossQty,wm_concat(distinct ci.notes) notes,0 ISCOMMISSION from containerstepattribute ci
        left join workflowstep ws on ws.workflowstepid=ci.workflowstepid
        left join specbase sb on sb.specbaseid=ws.specbaseid
        left join spec s on s.specid=nvl(sb.revofrcdid,ws.specid)
        left join specbase sb2 on sb2.specbaseid=s.specbaseid where not exists (select 1 from containersteprecord where containerid=ci.containerid and workflowstepid=ci.workflowstepid and Reportdate IS NOT NULL) 
and not exists (select 1 from containercommidetail cl where cl.containerid=ci.containerid and cl.workflowstepid=ci.workflowstepid and cl.isuse=0) </string>.Value

            strSql += String.Format(" and ci.containerid='{0}'", containerID)
            strSql += " and ci.notes is not null "
            strSql += <string> group by ci.containerid,ws.workflowid,ws.workflowstepid,ws.workflowstepname,sb2.specname,s.specid,s.iskey,ws.sequence,s.opdesc,s.specno,s.lastcheck,ws.controlcode,s.controlcode </string>.Value

        End If

        If conPrintType = "2" Then '只打印当前工艺的备注，和非当前工艺不加工的备注
            'add:Wangjh 20221013 批次备注也算加工记录
            strSql += <string> 
        union all
        select null factoryid,ci.containerid,ws.workflowid,ws.workflowstepid,ws.workflowstepname,sb2.specname,s.specid,s.iskey,null ReportQty,null ReportEmployeeName,
        null containerstepworkreportid,null reportdate,null checktype,null checkemployeename,null passqty,null lossqty,null defectqty,null ReworkQty,null usecompleted, ws.sequence,s.opdesc,
        s.specno,s.lastcheck,NVL(ws.controlcode,s.controlcode) controlcode,null checknote,null worknote,null SpecimenQty,null SpecimenLossQty,wm_concat(ci.notes) notes,NULL ISCOMMISSION from containerstepattribute ci
        left join workflowstep ws on ws.workflowstepid=ci.workflowstepid
        left join specbase sb on sb.specbaseid=ws.specbaseid
        left join spec s on s.specid=nvl(sb.revofrcdid,ws.specid)
        left join specbase sb2 on sb2.specbaseid=s.specbaseid 
where not exists (select 1 from containersteprecord where containerid=ci.containerid and workflowstepid=ci.workflowstepid and Reportdate IS NOT NULL) 
and not exists (select 1 from containercommidetail cl where cl.containerid=ci.containerid and cl.workflowstepid=ci.workflowstepid and cl.isuse=0)
</string>.Value
            strSql += String.Format(" and ci.containerid='{0}'", containerID)
            strSql += String.Format("  and (ws.workflowid='{0}' or (ws.workflowid!='{0}' and exists(select 1 from containerstepnoship cn where cn.containerid=ci.containerid and cn.workflowstepid=ci.workflowstepid and cn.isuse=0) ) ) ", workflowID)

            strSql += " and ci.notes is not null "
            strSql += <string> group by ci.containerid,ws.workflowid,ws.workflowstepid,ws.workflowstepname,sb2.specname,s.specid,s.iskey,ws.sequence,s.opdesc,s.specno,s.lastcheck,ws.controlcode,s.controlcode </string>.Value

        End If

        'add:Wangjh 20220412 有外委记录也算加工记录
        strSql += <string>
union all SELECT  null factoryid,cm.containerid,ws.workflowid,ws.workflowstepid,ws.workflowstepname,sb2.specname,s.specid,s.iskey,null ReportQty,null ReportEmployeeName,
null containerstepworkreportid,null reportdate,null checktype,null checkemployeename,null passqty,null lossqty,null defectqty,null ReworkQty,null usecompleted, ws.sequence,s.opdesc,
s.specno,s.lastcheck,NVL(ws.controlcode,s.controlcode) controlcode,null checknote,null worknote,null SpecimenQty,null SpecimenLossQty,wm_concat(ci.notes) notes,1 ISCOMMISSION FROM containercommissioning cm
inner JOIN containercommidetail cl ON cl.containercommissioningid=cm.containercommissioningid AND cl.isuse=0
LEFT JOIN workflowstep ws ON ws.workflowstepid=cl.workflowstepid
left join specbase sb on sb.specbaseid=ws.specbaseid
left join spec s on s.specid=nvl(sb.revofrcdid,ws.specid)
left join specbase sb2 on sb2.specbaseid=s.specbaseid
left join containerstepattribute ci on ci.ContainerID=cm.ContainerID and ci.WorkflowStepID=cl.WorkflowStepID and ci.notes is not null
WHERE cm.isuse=0 and cm.indate is not null AND not exists (select 1 from containersteprecord where containerid=cm.containerid and workflowstepid=cl.workflowstepid and Reportdate IS NOT NULL)
</string>.Value
        strSql += String.Format(" and cm.containerid='{0}'", containerID)
        strSql += <string> group by cm.containerid,ws.workflowid,ws.workflowstepid,ws.workflowstepname,sb2.specname,s.specid,s.iskey,ws.sequence,s.opdesc,s.specno,s.lastcheck,ws.controlcode,s.controlcode</string>.Value
        GetContainerStepWorkingRecord = strSql
    End Function
    ''' <summary>
    ''' 查询需要打印的转工完成记录
    '''  add：Wangjh 20180508
    ''' </summary>
    ''' <param name="containerID"></param>
    ''' <param name="workflowID"></param>
    ''' <param name="isTranfer"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function GetContainerStepShipWoringRecord(ByVal containerID As String, ByVal workflowID As String, ByVal isTranfer As Boolean) As String
        Dim strSql As String = <string>
                                  SELECT csre.containerid,ws.workflowid,s.specid,ws.workflowstepid,ws.workflowstepname,s.opdesc,csre.acceptanceqty PassQty,csre.acceptanceqty ReportQty,e.fullname ReportEmployeeName,e.fullname CheckEmployeeName,csre.recivedate reportdate
                                 ,(CASE WHEN csre.workflowstepid=ws.workflowstepid and  csre.lossqty!=0 THEN csre.lossqty ELSE NULL END) lossqty,
                                 (CASE WHEN csre.workflowstepid=ws.workflowstepid and csre.defectqty!=0 THEN csre.defectqty ELSE NULL END)  defectqty,'1' usecompleted,ws.sequence,s.opdesc,nvl(s.specno,SUBSTR(ws.workflowstepname,0,INSTR(ws.workflowstepname,'-')-1)) specno  FROM containershiprecive csre
                                 LEFT JOIN containershiprecordstep csrs ON csrs.containershiprecordid=csre.containershiprecordid
				                 LEFT JOIN workflowstep ws ON ws.workflowstepid=csrs.workflowstepid   
                                 LEFT JOIN specbase sb ON sb.specbaseid=ws.specbaseid
                                 LEFT JOIN spec s ON s.specid=NVL(sb.revofrcdid,ws.specid)
                                 LEFT JOIN employee e ON e.employeeid=csre.reciver
				                 LEFT JOIN containersteprecord csr  ON csr.containerid=csre.containerid AND csr.workflowstepid=csrs.workflowstepid
                                WHERE (NVL(ws.controlcode,s.controlcode)!='LY07' OR NVL(ws.controlcode,s.controlcode) IS NULL) AND csre.reciver IS NOT NULL AND csr.containersteprecordid IS NULL
                              </string>
        strSql += String.Format(" and csre.containerid='{0}'", containerID)
        'If isTranfer Then '查询传入工艺之外的
        '    strSql += String.Format(" and ws.workflowid<>'{0}'", workflowID)
        'Else
        '    strSql += String.Format(" and ws.workflowid='{0}'", workflowID)
        'End If
        GetContainerStepShipWoringRecord = strSql
    End Function

#Region "得到对应的关闭批次"
    Public Function GetColseMemo(ByVal strContianerName As String) As String Implements uMESContainerInfoPrintIBusiness.GetColseMemo
        Dim strMemo As String = ""
        'Dim strSql As String = "SELECT ue.attributevalue FROM userattribute ue WHERE ue.parentid='" & strContianerID & "' AND ue.userattributename='closeMemo'"

        Dim strSql As String = "SELECT ue.attributevalue FROM userattribute ue " & vbCrLf & _
                               "LEFT JOIN Container c ON c.containerid=ue.parentid " & vbCrLf & _
                               "WHERE c.containername='" & strContianerName & "'  AND ue.userattributename='closeMemo'"

        Dim dataTmp As DataTable = dal.StandardGetDataTable(strSql)
        If dataTmp.Rows.Count > 0 Then
            If dataTmp.Rows(0).IsNull("attributevalue") = False Then
                strMemo = dataTmp.Rows(0)("attributevalue")
            End If
        End If
        Return strMemo
    End Function
#End Region
    ''' <summary>
    ''' 查询主卡的分批信息
    ''' create:Wangjh 
    ''' </summary>
    ''' <param name="contianerId"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function GetSQL_Split(ByVal contianerId As String) As String
        GetSQL_Split = " SELECT ws.workflowstepname,SUBSTR( csi.splitcontainername,INSTR( csi.splitcontainername,'/',1)+1) splitcontainername,csi.qty,e.fullname splitemployeeName,e2.fullname checkemployeeName,csi.splitreason,csi.createdate FROM containersplitinfo csi " &
                    " LEFT JOIN workflowstep ws ON ws.workflowstepid=csi.workflowstepid" &
                    " INNER JOIN container c ON c.containerid=csi.containerid" &
                    " LEFT JOIN employee e ON e.employeeid=csi.splitemployeeid" &
                    " LEFT JOIN employee e2 ON e2.employeeid=csi.checkemployeeid"
        GetSQL_Split += String.Format(" WHERE csi.containerid='{0}' and csi.isuse=0 AND c.status=1 and csi.splitresult=0", contianerId)
        GetSQL_Split += " GROUP BY ws.workflowstepname,csi.splitcontainername ,csi.qty,e.fullname ,e2.fullname ,csi.splitreason,csi.createdate"
        GetSQL_Split += " ORDER BY csi.createdate"
    End Function
    ''' <summary>
    ''' 根据SUBWORKFLOWID或SUBWORKFLOWBASEID获取子工艺的工序
    ''' create:Wangjh
    ''' </summary>
    ''' <param name="ID"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function GetSQL_ChildWorkFlow(ByVal ID) As String
        Dim strSql = ""
        strSql += "SELECT NULL containerid,pb.productname,wf.workflowid,wb.workflowname,ws.workflowstepname,sb2.specname,s.specid," &
                      "  s.iskey,ws.sequence,cc.cdoname,WS.SUBWORKFLOWID,WS.SUBWORKFLOWBASEID FROM WORKFLOW WF" &
                      "  LEFT JOIN workflowbase wb ON wb.workflowbaseid=wf.workflowbaseid" &
                      "  LEFT JOIN workflowstep ws ON ws.workflowid=wf.workflowid" &
                       " left join specbase sb on sb.specbaseid = ws.specbaseid " &
                      "  left join spec s on s.specid =NVL( sb.revofrcdid ,ws.specid)" &
                      "  LEFT JOIN specbase sb2 ON sb2.specbaseid=s.specbaseid" &
                       " LEFT JOIN product p ON p.productid=wf.productid" &
                      "  LEFT JOIN productbase pb ON pb.productbaseid=p.productbaseid" &
                      "  LEFT JOIN cdodefinition cc ON cc.cdodefid=wf.cdotypeid  "
        strSql += String.Format(" WHERE  WF.WORKFLOWBASEID='{0}' OR WF.WORKFLOWID='{0}'", ID)

        strSql += " GROUP BY pb.productname,wf.workflowid,wb.workflowname,ws.workflowstepname,sb2.specname,s.specid,s.iskey,ws.sequence,cc.cdoname,WS.SUBWORKFLOWID,WS.SUBWORKFLOWBASEID" &
                 " order by ws.sequence DESC"
        GetSQL_ChildWorkFlow = strSql
    End Function

    ''' <summary>
    ''' 根据工艺ID获取工序信息
    ''' add:Wangjh 20180123
    ''' </summary>
    ''' <param name="workflowID"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetStepInfoByWorkFlowID(ByVal workflowID As String) As DataTable Implements uMESContainerInfoPrintIBusiness.GetStepInfoByWorkFlowID
        Dim strSql As String = " SELECT NULL containerid,NULL productname,NULL workflowid , wb.workflowname,wt.workflowstepid,WT.workflowstepname,sb1.specname,s.specid,s.iskey, WT.sequence,cc.cdoname,WT.SUBWORKFLOWID,WT.SUBWORKFLOWBASEID,s.opdesc,s.specno,WT.CONTROLCODE" &
                                ",null checktype,'' ReportQty,'' passqty,'' lossqty,'' ReportEmployeeName,'' reportdate,'' defectqty,'' checkemployeename " &
                                " FROM WORKFLOWSTEP WT" &
                               " LEFT JOIN SPECBASE SB ON SB.SPECBASEID = WT.SPECBASEID" &
                               " LEFT JOIN SPEC S ON S.SPECID = NVL(SB.REVOFRCDID, WT.SPECID)" &
                            " LEFT JOIN specbase sb1 ON sb1.specbaseid=s.specbaseid" &
                            " LEFT JOIN workflow w ON w.workflowid=wt.workflowid" &
                            " LEFT JOIN workflowbase wb ON wb.workflowbaseid=w.workflowbaseid" &
                            " LEFT JOIN cdodefinition cc ON cc.cdodefid=w.cdotypeid"
        strSql += String.Format(" WHERE WT.WORKFLOWID= '{0}'", workflowID)
        strSql += " ORDER BY WT.SEQUENCE"

        GetStepInfoByWorkFlowID = DatabaseHelper.Query_ReturnDatTable(strSql)
    End Function

    ''' <summary>
    ''' 根据母订单获取子订单
    ''' add:Wangjh 20180327
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetSonMfgOrder(ByVal para As Dictionary(Of String, String)) As DataTable Implements uMESContainerInfoPrintIBusiness.GetSonMfgOrder
        Return Nothing
    End Function

    ''' <summary>
    ''' 待打印的工序处理
    ''' add:Wangjh 20180516
    ''' </summary>
    ''' <param name="QueryCondition"></param>
    ''' <param name="dtContainerStep"></param>
    ''' <param name="ds"></param>
    ''' <remarks></remarks>
    Sub ContainerStepForPrint(ByVal QueryCondition As Dictionary(Of String, String), ByVal dtContainerStep As DataTable, ByRef ds As DataSet)
        Dim strSql As String = ""
        Dim conPrintType As String = "1" '1:工序执行检查 2:流水卡打印(入库只打干过的，在制情况只打当前工序之后)
        If QueryCondition.ContainsKey("ConPrintType") Then
            conPrintType = QueryCondition("ConPrintType")
        End If
        Dim dtContainerStepWorkRecord As DataTable = New DataTable '批次的所有加工记录
        strSql = GetContainerStepWorkingRecord(QueryCondition("Containerid"), QueryCondition("WorkFlowID"), True, conPrintType)
        dtContainerStepWorkRecord = DatabaseHelper.Query_ReturnDatTable(strSql)
        Dim dtContainerShipRecord = New DataTable '批次转工工序记录

        'strSql = GetContainerStepShipWoringRecord(QueryCondition("Containerid"), "", True)
        'dtContainerShipRecord = DatabaseHelper.Query_ReturnDatTable(strSql)
        'dtContainerStepWorkRecord.Merge(dtContainerShipRecord)

        dtContainerStepWorkRecord.AcceptChanges()

        '标记当前工艺及是否打印的子工艺
        Dim subWorkflow As DataTable = GetSQL_ContainerSubWorkflow(QueryCondition)
        Dim subWorkflows As DataRow() = subWorkflow.Select("subworkflowid='" + QueryCondition("CurWorkFlowID") + "'")
        If subWorkflows.Length > 0 Then '当前工艺是否是子工艺
            QueryCondition("IsSubworkFlow") = "1" '当前工艺是子工艺
            'QueryCondition("WorkFlowID") = subWorkflows(0)("WorkflowID")
            'strSql = GetWorkflowStepForPrint(QueryCondition)
            'dtContainerStep = DatabaseHelper.Query_ReturnDatTable(strSql)
        Else
            QueryCondition("IsSubworkFlow") = "0"
        End If
        '

        'If QueryCondition.ContainsKey("IsBlank") Then
        '    dtContainerStep.TableName = "dtContainerStep"
        '    ds.Tables.Add(dtContainerStep)
        '    Return
        'End If


        '查询批次主子工艺信息
        If QueryCondition("IsSubworkFlow") = "0" And (Not QueryCondition.ContainsKey("PdmSubWf")) Then '打印非子工艺
            Dim deleteList As List(Of Integer) = New List(Of Integer)
            For i As Integer = 0 To dtContainerStepWorkRecord.Rows.Count - 1
                If subWorkflow.Compute("count(containerid)", "subworkflowid='" + dtContainerStepWorkRecord.Rows(i)("workflowid").ToString + "'") > 0 Then '移除子工艺的加工记录
                    deleteList.Add(i)
                End If
            Next
            Dim n As Integer = 0
            For Each index As Integer In deleteList
                index = index - n
                dtContainerStepWorkRecord.Rows.RemoveAt(index)
                n += 1
            Next
            dtContainerStepWorkRecord.AcceptChanges()
        End If

        '
        '当前工艺的所有加工记录
        Dim curContainerStepWorkRecord As DataTable = New DataTable
        Dim dtTemp As DataTable = dtContainerStepWorkRecord.Copy()
        Dim dtTempDefv As DataView = dtTemp.DefaultView
        dtTempDefv.RowFilter = String.Format("workflowid='{0}'", QueryCondition("CurWorkFlowID"))
        curContainerStepWorkRecord = dtTempDefv.ToTable
        curContainerStepWorkRecord.TableName = "dtCurStepWorkRecord"
        If QueryCondition("IsSubworkFlow") = "0" Then
            ds.Tables.Add(curContainerStepWorkRecord)
        End If
        '

        '待打印工序追加可能有的加工记录
        Dim dtContainerStepTemp = dtContainerStep.Copy
        Dim delNum As Integer = 0
        For i As Integer = 0 To dtContainerStepTemp.Rows.Count - 1
            If curContainerStepWorkRecord.Compute("count(containerid)", "workflowstepid='" + dtContainerStepTemp.Rows(i)("workflowstepid").ToString + "'") > 0 Then
                dtContainerStep.Rows.RemoveAt(i - delNum)
                delNum += 1
            End If
        Next

        If (QueryCondition("IsSubworkFlow") = "0" And (Not QueryCondition.ContainsKey("PdmSubWf"))) Or QueryCondition("IsSubworkFlow") = "1" Then '当前工艺是主工艺且不打印子工艺时,当前是子工艺时，追加当前加工记录
            dtContainerStep.Merge(curContainerStepWorkRecord)
        Else
            curContainerStepWorkRecord.Rows.Clear()
            dtContainerStep.Merge(curContainerStepWorkRecord)
        End If
        dtContainerStep.DefaultView.Sort = " sequence asc"
        dtContainerStep = dtContainerStep.DefaultView.ToTable
        dtContainerStep.AcceptChanges()
        '

        '子工艺打印不用加
        If QueryCondition("IsSubworkFlow") = "0" And (Not QueryCondition.ContainsKey("PdmSubWf")) Then '当前工艺是主工艺且不打印子工艺时才追加加工记录
            Dim tempObj = dtContainerStepWorkRecord.AsEnumerable.Except(curContainerStepWorkRecord.AsEnumerable, DataRowComparer.Default)
            If tempObj.Count = 0 Then
                dtContainerStepWorkRecord.Rows.Clear()
            Else
                dtContainerStepWorkRecord = tempObj.CopyToDataTable
                dtContainerStepWorkRecord.DefaultView.Sort = " sequence asc"
                dtContainerStepWorkRecord = dtContainerStepWorkRecord.DefaultView.ToTable
                dtContainerStepWorkRecord.AcceptChanges() '此时加工记录只剩下当前工艺外所有的
            End If
            '移除其他工艺版本已经加工，且到现工艺没加工的工序记录
            delNum = 0
            Dim temDt = dtContainerStep.Copy
            For i As Integer = 0 To temDt.Rows.Count - 1
                If (Not temDt.Rows(i).IsNull("reportdate")) Or (Not String.IsNullOrWhiteSpace(temDt.Rows(i)("notes").ToString)) Then '有加工记录,有备注也算加工
                    Continue For
                End If
                If dtContainerStepWorkRecord.Compute("count(containerid)", "workflowstepname='" + temDt.Rows(i)("workflowstepname").ToString + "'") > 0 Then '已有加工记录时移除空白工序
                    dtContainerStep.Rows.RemoveAt(i - delNum)
                    delNum += 1
                End If
            Next
            '
            dtContainerStepWorkRecord.Merge(dtContainerStep) '加上当前所有工序及加工记录
            dtContainerStepWorkRecord.AcceptChanges()
            dtContainerStepWorkRecord.DefaultView.Sort = " specno asc"
            dtContainerStepWorkRecord = dtContainerStepWorkRecord.DefaultView.ToTable
            dtContainerStepWorkRecord.TableName = "dtContainerStep"
            ds.Tables.Add(dtContainerStepWorkRecord)
        Else
            dtContainerStep.TableName = "dtContainerStep"
            dtContainerStep.DefaultView.Sort = " specno asc"
            dtContainerStep = dtContainerStep.DefaultView.ToTable
            If Not ds.Tables.Contains("dtContainerStep") Then
                ds.Tables.Add(dtContainerStep)
            End If
        End If
        '
    End Sub

    ''' <summary>
    ''' 查询子工艺指定的工艺员
    ''' add:Wangjh 20180621
    ''' </summary>
    ''' <param name="dic"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetSubWorkflowCheckTechnique(ByVal dic As Dictionary(Of String, String)) As DataTable Implements uMESContainerInfoPrintIBusiness.GetSubWorkflowCheckTechnique
        Dim strSql As String = <string>
                                  SELECT e.fullname，e2.fullname planname,w.workflowid FROM subworkflowcheckinfo swc
                                    LEFT JOIN workflow w ON w.workflowid=swc.subworkflowid
                                    LEFT JOIN employee e ON e.employeeid=swc.checktechniqueid
                                    LEFT JOIN employee e2 ON e2.employeeid=swc.planemployeeid
                              </string>
        strSql += String.Format("WHERE swc.containerid='{0}' AND w.process_no='{1}'", dic("Containerid"), dic("PdmSubWf"))
        Return DatabaseHelper.Query_ReturnDatTable(strSql)
    End Function

    ''' <summary>
    ''' 当前序未子工艺查询process_no
    ''' </summary>
    ''' <param name="containerID"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetContainerCurrentSubWorkflow(ByVal containerID As String) As DataTable Implements uMESContainerInfoPrintIBusiness.GetContainerCurrentSubWorkflow
        Dim strSql As String = <string>
                                   SELECT w.process_no FROM containercurrentinfo cci
                                    LEFT JOIN subworkflowcheckinfo swc ON swc.containerid=cci.containerid AND swc.subworkflowid=cci.workflowid
                                    LEFT JOIN Workflow w ON w.workflowid=swc.subworkflowid
                                    WHERE w.process_no IS NOT NULL 
                               </string>
        strSql += String.Format(" AND cci.containerid='{0}'", containerID)
        Return DatabaseHelper.Query_ReturnDatTable(strSql)
    End Function
End Class
